Hlookup #N/A ????

[NewbWan]

=HLOOKUP(C3,G2:K3,1,FALSE)

This returns N/A and I do not understand it

row 1 holds the information I want returned to me
c3 holds a monetary value I want looked up between G and K
The formula in c3 looks between G3 and K3 and returns the Min.
What is wrong with this as it is not working....

If you need any thing else to help answer the question please ask.

I thank you for your time and appreciate any help that can be given.

 

[Ormus]

Does C3 match anything in the lookup row? Will return an NA if it does not find what is in C3

 

[NewbWan]

Yes, in fact C3 matches G3, I would expect to see G2 as the result.
After taking another look the formula in C3 is an ?array? has {} - does this matter?

 

[Jonmo1]

Hlookup can only match a value in the TOP row of the array (in this case, row2)
Then return a value from the specified row # going DOWN from the top.

If you want to return a value from row 2, based on a match in row 3, you need Index/Match..

=INDEX(G2:K2,MATCH(C3,G3:K3,0))

Hope that helps

 

[NewbWan]

Yes this does what I am looking for, right now, I am going to mess with it as the table I am playing with is much bigger then this portion I am testing with. However I do not understand why this works and Hlookup did not for it was the top row I want returned?

If you do not mind, is it better to use Index,Match over Hlookup or is this a case by case use your own judgment type choice? I ask because I have Hlookup working in other places as well as vlookup.

Thank you for your help and a solution to my problem.

 

[NewbWan]

I am a Dumb-***
I see now what you said - I was trying to use Hlookup reverse of what it is intended to do - but then why even use it if Index,match can give what you are looking for? - Because I will probably find out my answer by messing with the information but I will leave the question up anyway.

Thanks again Ormus and Jonmo1 for your help
and Jonmo1 for the answer

 

[Jonmo1]

I am a Dumb-***
I see now what you said - I was trying to use Hlookup reverse of what it is intended to do - but then why even use it if Index,match can give what you are looking for? - Because I will probably find out my answer by messing with the information but I will leave the question up anyway.

Thanks again Ormus and Jonmo1 for your help
and Jonmo1 for the answer

A good question....One that has been debated..

I suppose one answer would be:
Because Index/Match (used together) is not a designed, out of the box, built in function.
Where Vlookup and Hlookup are out of the box, built in functions. Documented in the help files.

It takes a bit of experience to learn how to combine Index and Match for that purpose

 

[NewbWan]

It takes a bit of experience to learn how to combine Index and Match for that purpose

And that is experience I have mooched from you my new found friend
Funny you should mention "out of the box" the thought crossed my mind that the only reason hlookup is around is so the layman like myself can get confused

 

[Jonmo1]

And that is experience I have mooched from you my new found friend

And that is the purpose of this forum.
Glad to help.

Funny you should mention "out of the box" the thought crossed my mind that the only reason hlookup is around is so the layman like myself can get confused

You said that, not me....