Lookup Formula not returning Expected Results

zfitzjarrell

Board Regular
Joined
Mar 9, 2005
Messages
114
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top