CODEWITHSUNDEEP
excelexcel-formulalookup
Dergyll
Dergyll

Reputation: 949

Excel: lookup multiple criteria but same column

here is a quick sample of the sheet:

enter image description here

How would I go about looking up multiple criteria? I want to look for the "candy" under "machine3" and return the value next column over ("1" in this case), or look for "cream" under "machine2".

I appreciate any tip on this.

Upvotes: 0

Views: 328

Answers (3)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

=IFERROR(INDEX($B$1:$B$14,SMALL(IF($A$1:$A$14=E2,IF(ROW($A$1:$A$14)>MATCH("machine"&D2,$A$1:$A$14,0),IFERROR(IF(ROW($A$1:$A$14)<MATCH("machine"&D2+1,$A$1:$A$14,0),ROW($A$1:$A$14)),ROW($A$1:$A$14)))),1)),"Not Found")

This is an array formula - use Ctrl+Shift+Enter while still in the formula bar.

Looks for the match of the product, then evaluates the row against the MATCH() of the machine number and the machine number above it and returns the smallest result.

enter image description here

Upvotes: 1

jblood94
jblood94

Reputation: 16981

See the image below. If by "under" you mean anywhere under, then use the formula in E2:

=VLOOKUP(D3,INDIRECT("A"&MATCH(D2,$A$1:$A$14,0)+1&":B14"),2,FALSE)

If by "under" you mean under the machine specified, but before the next machine, then use the formula in F2 (which returns "Not found" if it is not found before the next machine):

=IF(MATCH(D3,INDIRECT("A"&MATCH(D2,$A$1:$A$14,0)+1&":A14"),0)<MATCH("machine*",INDIRECT("A"&MATCH(D2,$A$1:$A$14,0)+1&":A14"),0),VLOOKUP(D3,INDIRECT("A"&MATCH(D2,$A$1:$A$14,0)+1&":B14"),2,FALSE),"Not found")

Upvotes: 1

Ganesh N
Ganesh N

Reputation: 1

Write the below formula in B2 Cell and filldown

=IF(AND(A1="machine3", A2="candy"),1,IF(AND(A1="machine2", A2="cream"),1,""))

Upvotes: -1

Related Questions