Vlookup more than one criterion

Hi,

Am trying to look up prices that I quoted a client recently to ensure that I
quote the same price. The problem is that the same part number may be
manufactured by more than one company, so I have to quote on those, not just
one.

Vlookup only finds the first one... Example simplified:

Part no. Mfg Price
A-100 ABC 2.00
A-100 XYZ 3.00
A-100 LMN 1.50
L-987 XYZ 0.75
L-987 MNO 0.70
L-987 ABC 0.71

Any ideas??
Thanks

--
Thanks!

Dee

Assuming that A2:C7 contains the data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX(C2:C7,MATCH(1,IF(A2:A7="A-100",IF(B2:B7="XYZ",1)),0))

Hope this helps!

In article <[email protected]>,
dee <[email protected]> wrote:

> Hi,
>
> Am trying to look up prices that I quoted a client recently to ensure that I
> quote the same price. The problem is that the same part number may be
> manufactured by more than one company, so I have to quote on those, not just
> one.
>
> Vlookup only finds the first one... Example simplified:
>
> Part no. Mfg Price
> A-100 ABC 2.00
> A-100 XYZ 3.00
> A-100 LMN 1.50
> L-987 XYZ 0.75
> L-987 MNO 0.70
> L-987 ABC 0.71
>
> Any ideas??
> Thanks

Thank you. I modified it slightly so that it referred to cell addresses as
opposed to specific content. I will also add something so that if a match
isn't found, I won't have the #n/a problem.

--
Thanks!

Dee


"Domenic" wrote:

> Assuming that A2:C7 contains the data, try the following formula which
> needs to be confirmed with CONTROL+SHIFT+ENTER...
>
> =INDEX(C2:C7,MATCH(1,IF(A2:A7="A-100",IF(B2:B7="XYZ",1)),0))
>
> Hope this helps!
>
> In article <[email protected]>,
> dee <[email protected]> wrote:
>
> > Hi,
> >
> > Am trying to look up prices that I quoted a client recently to ensure that I
> > quote the same price. The problem is that the same part number may be
> > manufactured by more than one company, so I have to quote on those, not just
> > one.
> >
> > Vlookup only finds the first one... Example simplified:
> >
> > Part no. Mfg Price
> > A-100 ABC 2.00
> > A-100 XYZ 3.00
> > A-100 LMN 1.50
> > L-987 XYZ 0.75
> > L-987 MNO 0.70
> > L-987 ABC 0.71
> >
> > Any ideas??
> > Thanks
>