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
|