lookup with IF criteria

C

cv8497

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
 
J

Jacob Skaria

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)))
 
C

cv8497

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="def",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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help With Sumif 3
Data Alignment in columns 2
SEARCH IN SUMPRODUCT 8
lookup duplicate value 7
Help with IF 6
Lookup Wizard 4
Splitting data from cells into multiple lines 6
Concatenating two formulas 4

Top