LOOKUP Function gives wrong results

[AndreasW]

I have the following sheet:
A B C D E F
1 qwe rtz uio asd fgh jkl
2 1 2 3 4 5 6
3
4 qwe =LOOKUP(A4;$A$1:$K$2)

The function in B4 should return "1", but it returns "6". The value changes
if I delete some columns (e.g. if I delete columns D,E, and F, I get a
correct value).
When I am using just "a", "b", "c" instead names like "qwe", "rtz",... I get
the correct values. What am I doing wrong?
I am using XP and Excel 2003

 

[Mike H]

Hi,

Is this what your trying to do

=HLOOKUP(A4,$A$1:F$2,2,FALSE)

Mike

 

[Mike H]

I forgot to mention my version of Excel uses , and not ; so change it to ;
after the A4

Mike

 

[AndreasW]

Yes, that's it. Thanks a lot.

I forgot to mention my version of Excel uses , and not ; so change it to ;
after the A4

Mike

 

[Ashish Mathur]

Hi,

Try this

=LOOKUP(A4,$A$1:$F$1,$A$2:$F$2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[barry houdini]

LOOKUP won't be appropriate here for two reasons:

1) lookup range isn't sorted ascending, in which case incorrect result
can occur

2) even if lookup range is sorted lookup can give a "closest match"
i.e. if lookup value isn't in the lookup range the greatest value les
than or equal to the lookup value is returned, I assume you want a
exact match.

I'd suggest HLOOKUP as per Mike H's suggestio

 

[Ashish Mathur]

Hi,

Thank for pointing that out - I should have mentioned that in my post.
Anyways, the following formula seems to resolve both the issues

=LOOKUP(2,1/($A$1:$F$1=A4),$A$2:$F$2)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com