MATCH Function - Help Please

[Joe Gieder]

I use the Match Function to determine values within
another sheet on my spreadsheet, is there another
Function that could be used in its place? The problem is
that with using Match you need to enter 1, 0 or -1 I use
0 because I need to find the exact match but I don't
always get it because it finds the first match based on
the criteria what I'm looking for is not always the
first one. The formula I use is:
{=IF(ISNA(INDEX(SuppliersR,MATCH(MIN(IF((SuppliersD=$C3)
*(SuppliersS<>0),SuppliersS)),SuppliersS,),0)),0,INDEX
(SuppliersR,MATCH(MIN(IF((SuppliersD=$C3)*
(SuppliersS<>0),SuppliersS)),SuppliersS,),0))}
Can anyone help please.
Thanks in advance
Joe

__________________________________________________________
Reply:

MATCH with match type parameter 0 will always return an
exact match (or
#N/A). If the match you want is not the first that
matches the criteria you
use, you need to specify the criteria more fully. You
need to describe your
data and what you are trying to achieve before anyone can
help you further.

__________________________________________________________
New message:

I have two worksheets, one called "Priced BOM" and the
other "Suppliers". I list all of my part numbers and
quantities on the "Priced BOM" worksheet and then enter
all the quotes I receive back from vendors on
the "Suppliers" worksheet. The problem is the "Suppliers"
sheet can have many differrent suppliers providing the
same part with different prices and sometimes differrent
parts but with the same extended cost. Which brings up my
dilema, by using "MATCH" it looks for my part number and
retrives the extended cost, then when it goes back to
fill in the "Priced BOM' sheet because I use the "0"
parameter it sometimes pulls over the wrong unit cost (if
it finds an earlier extended cost match than what it
found in the first place.)

What I'm trying to accomplish is to "MATCH" the part
numbers from the "Priced BOM" sheet with those on
the "Suppliers" sheet and retrieve the lowest overall
extended cost from the "Suppliers" worksheet and have it
fill in on the "Priced BOM" sheet.
I use the following array formula:

{=IF(ISNA(INDEX(SuppliersR,MATCH(MIN(IF((SuppliersD=$C68)*
(SuppliersS<>0),SuppliersS)),SuppliersS,),0)),0,INDEX
(SuppliersR,MATCH(MIN(IF((SuppliersD=$C68)*
(SuppliersS<>0),SuppliersS)),SuppliersS,),0))

SuppliersR = the unit cost range on the "Suppliers" sheet
SuppliersD = part number range on the "Suppliers" sheet
SuppliersS = extended cost range on the "Suppliers" sheet
$C68 = part number on the "Priced BOM" sheet

Sorry for the lengthy discusion here but I'm just about
at wits end and look to all and any of you that may be
able to help. I can email a sample of what I have
accomplished so far if it would help.

Thanks in advance
Joe

 

[Paul]

__________________________________________________________
Reply:

MATCH with match type parameter 0 will always return an
exact match (or
#N/A). If the match you want is not the first that
matches the criteria you
use, you need to specify the criteria more fully. You
need to describe your
data and what you are trying to achieve before anyone can
help you further.

__________________________________________________________
New message:

I have two worksheets, one called "Priced BOM" and the
other "Suppliers". I list all of my part numbers and
quantities on the "Priced BOM" worksheet and then enter
all the quotes I receive back from vendors on
the "Suppliers" worksheet. The problem is the "Suppliers"
sheet can have many differrent suppliers providing the
same part with different prices and sometimes differrent
parts but with the same extended cost. Which brings up my
dilema, by using "MATCH" it looks for my part number and
retrives the extended cost, then when it goes back to
fill in the "Priced BOM' sheet because I use the "0"
parameter it sometimes pulls over the wrong unit cost (if
it finds an earlier extended cost match than what it
found in the first place.)

What I'm trying to accomplish is to "MATCH" the part
numbers from the "Priced BOM" sheet with those on
the "Suppliers" sheet and retrieve the lowest overall
extended cost from the "Suppliers" worksheet and have it
fill in on the "Priced BOM" sheet.
I use the following array formula:

{=IF(ISNA(INDEX(SuppliersR,MATCH(MIN(IF((SuppliersD=$C68)*
(SuppliersS<>0),SuppliersS)),SuppliersS,),0)),0,INDEX
(SuppliersR,MATCH(MIN(IF((SuppliersD=$C68)*
(SuppliersS<>0),SuppliersS)),SuppliersS,),0))

SuppliersR = the unit cost range on the "Suppliers" sheet
SuppliersD = part number range on the "Suppliers" sheet
SuppliersS = extended cost range on the "Suppliers" sheet
$C68 = part number on the "Priced BOM" sheet

Sorry for the lengthy discusion here but I'm just about
at wits end and look to all and any of you that may be
able to help. I can email a sample of what I have
accomplished so far if it would help.

Thanks in advance
Joe

OK, I now understand what you're saying - that the part of your formula
that's going wrong is
MATCH(MIN(IF((SuppliersD=$C68)*(SuppliersS<>0),SuppliersS)),SuppliersS,0)
This is because what you are matching (the result of MIN) is not unique.

So, the answer is to make it unique. You can do this by matching not just
the result of MIN, but the part number concatenated with the result of MIN.
Of course, you then have to match not just in SuppliersS but in SuppliersD
concatenated with SuppliersS. Something like this:
=MATCH($C68&MIN(IF((SuppliersD=$C68)*(SuppliersS<>0),SuppliersS)),SuppliersD
&SuppliersS,0)

 

[Paul]

Paul,
Thank you very much for looking at and helping me with
this problem. It seems so easy now that by adding the
additional criteria it narrows the search to a specfic
part only. Again I realy appreciate your time, help and
effort. I hope I can someday repay the help.

Joe

Glad to hear it worked.