HLookUp

[DJ]

Hi All,


Range A1:E1 has values 100,200,300,400,500 and Range A2:E2 has values Agent1,Agent2,Agent3,Agent4,Agent5. Now somewhere in the sheet I apply the formula =HLOOKUP("Agent4",CHOOSE({2,1},A1:E1,A2:E2),2,0) but it is giving me #N/A error. I don't know why it is not giving me the right answer.


If I arrange above mentioned data vertically and apply Vlookup in similar manner it works.


Please help my why Choose() is working with VLookUp but not with HLookUp.


Thanks,

DJ

 

[prazad82]

Hello DJ,


I would use INDEX-MATCH for lookup. The advantage is we can lookup for a value that is above or below the lookup_value. Try this formula.


=INDEX(A$1:E$1,MATCH(D2,A2:E2,0))


regards,

Prazad

 

[DJ]

Hi Prazad82,


Thank you for the speedy response but I was aware with this solution.


I want to use Hlookup because of my report structure as I don't want to add helper rows. Also, wanted to know why HLoopUp is not working with Choose function whereas it is working with Vlookup.


Please note that the data I mentioned in my first post is just a sample data as I am unable to past my official report.


Thanks,

DJ

 

[prazad82]

Hello DJ,


If you still need to use the Hlookup-Choose function, here it is:


=HLOOKUP("Agent4",CHOOSE({2;1},A1:E1,A2:E2),2,0)


USE semicolon instead of a comma.


regards,

Prazad

 

[sgmpatnaik]

@DJ


HI


Why not you put the Agent Codes from A1:E1 and write the numbers in A2:E2 and try the formula as in A5


=HLOOKUP("Agent4",A1:E2,2,FALSE)


Hope it will clear your problem other wise just inform


Thanks


SP

 

[DJ]

Hi Prazad82,


This solution worked. Thank you for speedy resolution.


Sgmpatnaik: Thank you for your reponse but this is what I had used in my report.


Thanks again,

DJ