Lookup Function

[jordanpcpre]

I am having massive amounts of trouble with the =Lookup function. Here is the
formula that I'm using

=LOOKUP(A1,B1:B20,C1:C20)

The formula should find A1 within B1:B20, and then reference the the cell
just below it. So, A1=JJ and B13=JJ and C13=13. C20=20, and the Lookup
function keeps giving me 20! All the cells in B1:B20 are formulas, so I
think this is messing the formula up because when I don't use a formula in
B1:B20, the Lookup shows 13 (the correct #).

Please help! Thank you!

 

[Pete_UK]

Try it this way:

=HLOOKUP(A1,$B$1:$C$20,2,0)

This looks for an exact match, so it will return #N/A if one isn't
found. If you do not want an exact match, then you can try it like
this:

=HLOOKUP(A1,$B$1:$C$20,2)

but then the values in B1:B20 have to be in ascending order.

Hope this helps.

Pete

 

[Bernard Liengme]

I cannot reproduce these, even with formulas in column C
Are the B values really text - they are not numbers that have been formatted


If the B's are in ascending order, try this =VLOOKUP(A1,B1:C20,2,TRUE) for a
'closest' match
and tell us if it works for you
I they are not ordered use =VLOOKUP(A1,B1:C20,2,FALSE) to get a result only
for an exact match

best wishes