lookups and match

[Lisa]

Hi. I'm trying to do a lookup in a table; however, I'm stuck. I tried doing a
match and index, but I can't get it to work.

IE... For Product A, Vendor 2 would charge ____? And so forth.

Vendor
Product 1 2 3
A $5 $7 $9
B $8 $7 $6

Thanks so much,
Lisa

[Don Guillett]

more details on your problem along with examples of what you tried.

--
Don Guillett
SalesAid Software
[email protected]
"Lisa" <[email protected]> wrote in message
news:[email protected]...
> Hi. I'm trying to do a lookup in a table; however, I'm stuck. I tried
> doing a
> match and index, but I can't get it to work.
>
> IE... For Product A, Vendor 2 would charge ____? And so forth.
>
> Vendor
> Product 1 2 3
> A $5 $7 $9
> B $8 $7 $6
>
> Thanks so much,
> Lisa

[Toppers]

=INDEX($B$2:$D$3,MATCH(F1,$A$2:$A$3,0),MATCH(F2,$B$1:$D$1,0))

where F1 = Product
F2 = Vendor
$B$1:$D$1 are the vendors
$B$2:$D$3 are your prices
$A$2:$A$3 are the Products

HTH

"Don Guillett" wrote:

> more details on your problem along with examples of what you tried.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Lisa" <[email protected]> wrote in message
> news:[email protected]...
> > Hi. I'm trying to do a lookup in a table; however, I'm stuck. I tried
> > doing a
> > match and index, but I can't get it to work.
> >
> > IE... For Product A, Vendor 2 would charge ____? And so forth.
> >
> > Vendor
> > Product 1 2 3
> > A $5 $7 $9
> > B $8 $7 $6
> >
> > Thanks so much,
> > Lisa
>
>
>

[Chip Pearson]

Lisa,

Assuming that your product names are in column A starting in row
2, and vendor numbers are in row 1, use a formula like

=OFFSET(A1,MATCH("a",A2:A10,0),MATCH(2,B1:D1,0))

This will look up product "a" and vendor 2.

See the Double Lookups section at
www.cpearson.com/excel/lookups.htm for more details on various
techniques to look up data in tables.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Lisa" <[email protected]> wrote in message
news:[email protected]...
> Hi. I'm trying to do a lookup in a table; however, I'm stuck. I
> tried doing a
> match and index, but I can't get it to work.
>
> IE... For Product A, Vendor 2 would charge ____? And so forth.
>
> Vendor
> Product 1 2 3
> A $5 $7 $9
> B $8 $7 $6
>
> Thanks so much,
> Lisa

[Lisa]

You're the best. Thanks so much!

"Toppers" wrote:

> =INDEX($B$2:$D$3,MATCH(F1,$A$2:$A$3,0),MATCH(F2,$B$1:$D$1,0))
>
> where F1 = Product
> F2 = Vendor
> $B$1:$D$1 are the vendors
> $B$2:$D$3 are your prices
> $A$2:$A$3 are the Products
>
> HTH
>
> "Don Guillett" wrote:
>
> > more details on your problem along with examples of what you tried.
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Lisa" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi. I'm trying to do a lookup in a table; however, I'm stuck. I tried
> > > doing a
> > > match and index, but I can't get it to work.
> > >
> > > IE... For Product A, Vendor 2 would charge ____? And so forth.
> > >
> > > Vendor
> > > Product 1 2 3
> > > A $5 $7 $9
> > > B $8 $7 $6
> > >
> > > Thanks so much,
> > > Lisa
> >
> >
> >

[Lisa]

Thanks. I couldn't get this to work, but Topper's worked. Thanks again for
your help. :-)

"Chip Pearson" wrote:

> Lisa,
>
> Assuming that your product names are in column A starting in row
> 2, and vendor numbers are in row 1, use a formula like
>
> =OFFSET(A1,MATCH("a",A2:A10,0),MATCH(2,B1:D1,0))
>
> This will look up product "a" and vendor 2.
>
> See the Double Lookups section at
> www.cpearson.com/excel/lookups.htm for more details on various
> techniques to look up data in tables.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Lisa" <[email protected]> wrote in message
> news:[email protected]...
> > Hi. I'm trying to do a lookup in a table; however, I'm stuck. I
> > tried doing a
> > match and index, but I can't get it to work.
> >
> > IE... For Product A, Vendor 2 would charge ____? And so forth.
> >
> > Vendor
> > Product 1 2 3
> > A $5 $7 $9
> > B $8 $7 $6
> >
> > Thanks so much,
> > Lisa
>
>
>

[Alan Beban]

Lisa wrote:
> Hi. I'm trying to do a lookup in a table; however, I'm stuck. I tried doing a
> match and index, but I can't get it to work.
>
> IE... For Product A, Vendor 2 would charge ____? And so forth.
>
> Vendor
> Product 1 2 3
> A $5 $7 $9
> B $8 $7 $6
>
> Thanks so much,
> Lisa
Excel's Intersection Operator (a space) is designed for this purpose.

Highlight your table and click Insert|Name|Create and check Top row,
Left column.

Then, e.g., =ProductA Vendor2 will return the corresponding price

Note that the product names and vendor names need to be in a form
acceptable for range names in a worksheet.

Alan Beban