# Lookup Formula not returning Expected Results

#### zfitzjarrell

##### Board Regular
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try:

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

try adding the "false" for the last paramater.

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.

btmonroe said:
try adding the "false" for the last paramater.

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

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

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.

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

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.

Replies
3
Views
310
Replies
8
Views
705
Replies
3
Views
727
Replies
9
Views
243
Replies
2
Views
165

1,196,409
Messages
6,015,105
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back