look up multiple criteria

[bkunes]

I want to search sheet 2 for both column a and d in sheet 1, and return
column c of sheet 2

sheet 1
a b c d
KD083 12 1 SC513-0001-67.81

sheet 2
a b c
KD082 SCV219-0001-72 4
KD082 SC092/093HB 1
KD082 IT1414/1714 1
KD082 SC513-0001-67.81 1
KD082 DT1414/1714 1
KD082 SCV220-0001-48 4
KD083 SC513-0001-67.81 1
KD083 T-790-68 1
KD083 IT1415/1715 1
KD083 SC092/093HB 1
KD083 SC511-0001-67.81 1


--

 

[Rick Rothstein \(MVP - VB\)]

Try this array-entered** formula...

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet1!A1=Sheet2!A1:A10)*(Sheet1!D1=Sheet2!B1:B10),0))

** Commit the formula by pressing Ctrl+Shift+Enter, not just Enter.

Rick

 

[Bob Phillips]

=INDEX(Sheet2!C1:C20,MATCH(1,(A1=Sheet2!A1:A20)*(D1=Sheet2!B1:B20),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bkunes]

thanks it worked
--

Try this array-entered** formula...

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet1!A1=Sheet2!A1:A10)*(Sheet1!D1=Sheet2!B1:B10),0))

** Commit the formula by pressing Ctrl+Shift+Enter, not just Enter.

Rick