Invalid hlookup result if range lookup true

Question

I've problem with Microsoft Excel HLOOKUP function as below

As you can see, the result is #N/A for function that set true as lookup range

Where I'm expecting is should be 25 since it should find Mangga correctly (Like when it's false)

But searching Semangka would be as expected

Is there something that I miss?

Thanks and regards,

Your first formula working fine. Why you are using `Approximate` match? — Harun24hr, Jun 24 '21 at 04:14
@Harun24HR it's for teaching reason. So I need to test all combination — Adhi Kristianto, Jun 24 '21 at 04:27
@ScottCraner But why the result if I search for Semangka is as expected for those two function, 40. But why Mangga didn't gave me same result — Adhi Kristianto, Jun 24 '21 at 04:27
Because S is after A... put the data in alphabetical order and test again. — Solar Mike, Jun 24 '21 at 04:29
@SolarMike But there's Nama toko, which is started with N, in the front. Why searching for Semangka didn't effected — Adhi Kristianto, Jun 24 '21 at 04:31
No, you have not put the data alphabetical as @ScottCraner suggested. — Solar Mike, Jun 24 '21 at 04:37
@SolarMike so the Nama toko, which is started with N, in the front are excused by the formula? Or it's just: The unsorted data, makes the result unpredicted? — Adhi Kristianto, Jun 24 '21 at 04:57
If you are going to teach this then you should put in the effort of testing hlookup(), vlookup() and xlookup() as well as the use of index() with match() as they all have different benefits. And you should test the effects of re-sorting your data. — Solar Mike, Jun 24 '21 at 04:59
@SolarMike yes, I'm fully aware of that. But, I expect my students will ask me, If we should order our data first, why searching Semangka would be fine but Mangga wouldn't. When there's N (Nama toko) in the front in both searching Semangka and Mangga — Adhi Kristianto, Jun 24 '21 at 05:35
That is what **you** will find out by testing and/or reading the Excel help entry for each function. And, some of us may well have tested it ourselves if you had provided data instead of images... — Solar Mike, Jun 24 '21 at 05:56

score 1 · Answer 1 · answered Jun 24 '21 at 04:17

1

While HLOOKUP() will work with Exact_Match you can also use INDEX/MATCH like-

=INDEX(A2:E5,2,MATCH(A7,A1:E1,0))

answered Jun 24 '21 at 04:17

Harun24hr

30,391
4
21
36

Yes I know that, but my question is why my formula didn't worked as I expected – Adhi Kristianto Jun 24 '21 at 04:25
@AdhiKristianto `Approximate_Match` only works for sorted data. But `XLOOKUP()` works on unsorted data. – Harun24hr Jun 24 '21 at 04:28
But why searching Semangka will both give me 40 for both true and false. Question is updated – Adhi Kristianto Jun 24 '21 at 04:33
@AdhiKristianto Why not? You are referencing same row `3` for both formula and same column `Semangka`. `approximate_match` specially works for numeric value. If you want to teach your student about `approximae_match` then use numeric value instead of text. – Harun24hr Jun 24 '21 at 04:48
so the Nama toko, which is started with N, in the front are excused by the formula? Or it's just: The unsorted data, makes the result unpredicted? – Adhi Kristianto Jun 24 '21 at 04:58

Invalid hlookup result if range lookup true

1 Answers1