Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups and match
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups and match
more details on your problem along with examples of what you tried.
-- Don Guillett SalesAid Software "Lisa" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups and match
=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 "Lisa" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups and match
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups and match
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 "Lisa" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups and match
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups and match
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you perform lookups when the info is always moving? | New Users to Excel | |||
Lookup across 2 or more columns to match a row | Excel Worksheet Functions | |||
Lookups | Excel Discussion (Misc queries) | |||
Lookups vs Match | Excel Worksheet Functions | |||
Listing Multiple Rows from Match | Excel Worksheet Functions |