lookup with IF criteria

I am needing to look up data from a seperate report in which the VLOOKUP
formula will not work as there are multiple instances of the lookup value in
such. I would like to pull in specific results. Example - below is the
table_array in which I want to pull in the result where 'jim' is either "def"
or "jkl", first occurance of such is fine. is this possible with a formula?
I then want to pull in the corresponding number in column c. Any suggestions
are appreciated!

a b c
1 jim abc 123
2 jim def 456
3 andy ghi 789
4 jim jkl 101
5 jim mno 111

Try one of these formulas...

'will sum up the values of c1:c5 with matching critieras...So if there is
only one instance then this would work
=SUMPRODUCT((A1:A5="jim")*(B1:B5="def")*C1:C5)

'will return the first matching instance whatsoever
=INDEX(C1:C5,SUMPRODUCT((A1:A5="jim")*(B1:B5="def" )*ROW(C1:C5)))


--
Jacob (MVP - Excel)


"cv8497" wrote:

I am needing to look up data from a seperate report in which the VLOOKUP
formula will not work as there are multiple instances of the lookup value in
such. I would like to pull in specific results. Example - below is the
table_array in which I want to pull in the result where 'jim' is either "def"
or "jkl", first occurance of such is fine. is this possible with a formula?
I then want to pull in the corresponding number in column c. Any suggestions
are appreciated!

a b c
1 jim abc 123
2 jim def 456
3 andy ghi 789
4 jim jkl 101
5 jim mno 111

In the second formula below, would it be possible to nest an OR formula in
the 2nd part of the sumproduct formula?
=INDEX(C1:C5,SUMPRODUCT((A1:A5="jim")*(or(B1:B5="d ef",b1:b5=�jkl�))*ROW(C1:C5)))???

I have multiple criteria i am searching for and it only needs to be on of
them, but there may not be all of them, just a few of the criteria. Same
example below but say I wanted to look for "tuv", "wxy" or "def". it can be
any one of these. do i need to do an IF with numerous index formulas?

"Jacob Skaria" wrote:

Try one of these formulas...

'will sum up the values of c1:c5 with matching critieras...So if there is
only one instance then this would work
=SUMPRODUCT((A1:A5="jim")*(B1:B5="def")*C1:C5)

'will return the first matching instance whatsoever
=INDEX(C1:C5,SUMPRODUCT((A1:A5="jim")*(B1:B5="def" )*ROW(C1:C5)))


--
Jacob (MVP - Excel)


"cv8497" wrote:

I am needing to look up data from a seperate report in which the VLOOKUP
formula will not work as there are multiple instances of the lookup value in
such. I would like to pull in specific results. Example - below is the
table_array in which I want to pull in the result where 'jim' is either "def"
or "jkl", first occurance of such is fine. is this possible with a formula?
I then want to pull in the corresponding number in column c. Any suggestions
are appreciated!

a b c
1 jim abc 123
2 jim def 456
3 andy ghi 789
4 jim jkl 101
5 jim mno 111