Problems with the VLOOKUP formula
 

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

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_t...Excel/298.html
http://www.contextures.com/xlFunctions03.html



"Peter" wrote in message
...
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

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

"Peter" wrote:

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