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



"Peter" <takisa@comcast.net> wrote in message
news:itWdnZx_q9JU_bPfRVn-vA@comcast.com...
>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
>
>
>
>
>

[Michael]

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

[duane]

and by using false the list does not need to be in ascending order.............