CODEWITHSUNDEEP
if-statementexcel-formulamatch
Victoria G
Victoria G

Reputation: 81

IF MATCH with multiple lookups

Looking to create an IF(MATCH) formula on Sheet 2 of my workbook that will look up values from 5 columns on Sheet 1and return the corresponding text. For example if the value from column M on Sheet 2 matches any of the values from column J on Sheet 1 it would return "N", if not it would look in column K and if matched anything there it would return D, and so on. The formula I tried below only seems to work for the first value (N), but none of the others. 

=IFERROR(IF(MATCH(M2,Sheet1!$J:$J,0)>0,"N", 
IF(MATCH(M2,Sheet1!$K:$K,0)>0,"D", 
IF(MATCH(M2,Sheet1!$L:$L,0)>0,"R", 
IF(MATCH(M2,Sheet1!$M:$M,0)>0,"G", 
IF(MATCH(M2,Sheet1!$N:$N,0)>0,"F", ""))))), "")

Upvotes: 0

Views: 59

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

You need to check whether each MATCH is an ERROR:

=IFERROR(IF(ISNUMBER(MATCH(M2,Sheet1!$J:$J,0)),"N", 
         IF(ISNUMBER(MATCH(M2,Sheet1!$K:$K,0)),"D", 
         IF(ISNUMBER(MATCH(M2,Sheet1!$L:$L,0)),"R", 
         IF(ISNUMBER(MATCH(M2,Sheet1!$M:$M,0)),"G", 
         IF(ISNUMBER(MATCH(M2,Sheet1!$N:$N,0)),"F",""))))), "")

If the data set is not too large and you can set define the extants the formula can be simplified to:

=IFERROR(CHOOSE(AGGREGATE(15,6,(COLUMN($J$1:$N$1000)-MIN(COLUMN($J$1:$N$1000)+1)/($J$1:$N$1000=M2),1),"N","D","R","G","F"),"")

Upvotes: 1

Related Questions