Lookup Formula not returning Expected Results

[zfitzjarrell]

I wrote a formula that is basically looking for a name in Sheet 1, and returning the value in Column A. Here is the formula that I have:

=LOOKUP(A195,Sheet1!$F$1:$F$541,Sheet1!$D$1:$D$541)

The value I'm getting in return is not the correct match. Is this happening because I'm dealing with text as my lookup value or is there something else happening?


thanks

 

[NBVC]

Try:

=Index($D$1:$F$541,MATCH(A195,$F$1:$F$541,0),1)

 

[btmonroe]

try adding the "false" for the last paramater.

 

[Seti]

From the help file:

Important The values must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

So you probably don't have sorted data. Therefore Index/Match is your best bet.

Also, there is no False option with Lookup, only VLookup and HLookup.

 

[Brian from Maui]

try adding the "false" for the last paramater.

LOOKUP has no 4th argument. The lookup table has to be sorted in an ascending order.

 

[zfitzjarrell]

I didn't realize the Lookup had to be sorted. Thanks for that info.

As for the Index funtion NBVC suggested, I tried that but was unable to located what I was looking for. My result is an #N/A.

Any ideas?

thanks

 

[NBVC]

I forgot to insert the Sheet1! for your lookup ranges, sorry

=Index(Sheet1!$D$1:$F$541,MATCH(A195,Sheet1!$F$1:$F$541,0),1)

This formula assumes your table is in D1:F541 of Sheet1 and your are looking for the value from A195 in column F of your table and returning result from column D in sheet1.

Adjust as necessary.

Index(Lookup_table,match(lookup_value,lookup_array,0),column#of lookup_table to get value from)

 

[zfitzjarrell]

That worked. Thanks.

I figured out that part of my problem was my lookup value on the different sheets weren't EXACTLY the same, so that's why I continued to get #N/A Values.