Lookup functions not working

[thedon_1]

I am trying to use a lookup function, something i have done many times before.

I have a list called lookup list and another called data list.

I basically need to look at the first entry in the lookup list and check if it appears anywhere in the data list. That's all i need.

Problem is, sometimes lookups don't work, and when they do work, they just return a value that isn't even in the sheet!

This is something i do at work and i have a feeling that because most of my data comes of cognos or SAP, the formatting is messing with my formulas but it's just a theory.


I have included a file with data, any help would be great.

[MarvinP]

Hi,
The dollar signs (you don't have any) are getting you. You have:

=LOOKUP(D5,B5:B1793)

and may need.

=LOOKUP(D5,B$5:B$1793)

Then there is another argument in lookup telling it to only return exact matches. Otherwise it returns the first value less than the lookuped search.

Try using VLookup( ) with the third argument asking for EXACT match.
http://www.techonthenet.com/excel/formulas/vlookup.php

[thedon_1]

Hi, it's not the dollar signs. I know i need to use those when dragging the formula down for all of the values in lookup list, but in the sheet i uploaded, the lookup doesn't even work for the first value.

Thanks for replying though, forgetting the dollar signs has caught me out before, but unfortunately in this case i can't even get that far.

[NBVC]

Try:

=ISNUMBER(MATCH(D5,$B$5:$B$1793,0))

this returns TRUE next to the column D cell, if a match is found and FALSE if not found.

[thedon_1]

Nice, that seems to work. Could you please explain what's happening a little bit?

I use the isnumber function on my data and it returns false, so my data is not being looked at as numbers by Excel. How does your formula overide this?

Also, how would i turn my values into numbers (if there's a way)?


Thanks

[NBVC]

The first part of the formula to get evaluated is the MATCH function... Match looks for a match for a given value in a given 1 dimensional array. It returns the position number within that array where the match occurs. If a match does not occur, then you will get an #N/A error. The ISNUMBER() part simply checks if Match returned a number (to indicate a match is found) and results in TRUE if it is found.

If you want the actual value matched returned you could add the INDEX function

=IFERROR(INDEX($B$5:$B$1793,MATCH(D5,$B$5:$B$1793,0)),"")