HLOOKUP Function problem [SOLVED]

[paulpap]

Hi all,

I am trying to use to HLOOKUP function on the following example:

In row 1 I have text

In row 2 I have numbers


C1 C2 C3 C4

10 20 30 40


In A4 I give the number 30


How can I use HLOOKUP to return the C3 in A5?

HLOOKUP(A4;A1:D2;1;FALSE) does not work (gives me a #N/A)


Can you please help me?

Thanks a lot

 

[NARAYANK991]

Hi Paul ,


The HLOOKUP function needs to find the lookup value in the first row of the range.


In your case , you say that the first row , row 1 , has text ; clearly , the value 30 is not to be found in the first row. Hence your HLOOKUP will return #N/A.


Since the numbers are in the second row , using HLOOKUP will make your formula complex ; it is easier to use INDEX + MATCH as follows :


=INDEX(A1:D1;MATCH(A4;A2:D2;0))


If you really want to use the HLOOKUP function , try this :


=HLOOKUP(A4,CHOOSE({2;1},A1:D1,A2:D2),2,FALSE)


Narayan

 

[paulpap]

Hi Narayan,


Thanks for your immediate answer.

Of course I preferred the 1st formula and it worked fine.


Paul