Problems with the VLOOKUP formula

[Peter]

I am working on a worksheet that contains multiple tabs.
The tabs contain unique part numbers and values.

I have created a summary worksheet that contain all of the part numbers.
I am trying to create a formula that will compare the part number in the
summary sheet and match that part number to a different sheet and assign the
proper value.
So far I have not had great success with VLOOKUP. It's working but, it's
not reliable - meaning that if a part number in the summary sheet is larger
than a number in the other worksheet, it returns the larger value. If the
list is not in order it brings back the first value, and when there is a
dublicate number (maybe by accident) it will bring only the first one.

Is there another way? I am thinging "Match" , "Index", but I am no sure how
to use them properly.

Any help will be greatly appreciated
Peter

 

[Steve R]

I'm a fan of Index and Match so, may be a little biased towards using it
when other lookup functions may perform equally. There are many sites with
instructions on its use:

From Microsoft:

http://support.microsoft.com/kb/214142/EN-US/

Alternatives I find easier to understand:

http://www.exceltip.com/st/Combine_the_Index_and_Match_formulas_in_Microsoft_Excel/298.html
http://www.contextures.com/xlFunctions03.html

 

[Guest]

Hi Peter
Are you using FALSE in your VLOOKUP formula ??
eg, =VLOOKUP(cell,range,column, FALSE)
If you are using TRUE, it will give the next nearest answer if it can't find
an exact match

HTH
Michael