Lookup? for more than one criteria
I have a ss that contains the following:
(sheet1) #1. my source starts on sheet1 in cell b2 (this displays the company name). #2. (sheet2) #2a. names of companies from col h1:z1 product code list: b1:b50 #3. prices listed under the column for each company h1:h50, i1:i50, etc. What I want the formula to do is look at #1, for the company name, match to #2 on sheet2 ... Look at the product code on sheet1 and match to sheet2 product code then find the correct price for the item & company listed. Is this possible in a formula? Thanks for any suggestions. |
Lookup? for more than one criteria
Saved from a previous post:
I like this syntax: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. Annette wrote: I have a ss that contains the following: (sheet1) #1. my source starts on sheet1 in cell b2 (this displays the company name). #2. (sheet2) #2a. names of companies from col h1:z1 product code list: b1:b50 #3. prices listed under the column for each company h1:h50, i1:i50, etc. What I want the formula to do is look at #1, for the company name, match to #2 on sheet2 ... Look at the product code on sheet1 and match to sheet2 product code then find the correct price for the item & company listed. Is this possible in a formula? Thanks for any suggestions. -- Dave Peterson |
Lookup? for more than one criteria
Try this:
=INDEX(Sheet2!H1:Z50,MATCH(B3,Sheet2!B1:B50,0),MAT CH(B2,Sheet2!H1:Z1,0)) I'm assuming that your product list and price actually starts in row 2 not row 1. I'm also assuming that cell B3 on sheet1 contains the product you want to look up. |
All times are GMT +1. The time now is 02:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com