CODEWITHSUNDEEP
excelif-statementnestedmatch
Andrew
Andrew

Reputation: 1818

Using Match in Nested If Statement - only works on first IF Statement

Using Excel 2007

I have data like this:

column 1     column 2     column 3
========     ========     ========
type 10      Type 3       Type 5
Type 2       Type 12      Type 8
Type 4       Type 3       Type 6
Type 1       Type 5       Type 11

And I want to put one column at the end that decodes the 3 columns and displays the column position of any of the types from 1-3. Using a column with MATCH and nested IF statement. So the decode column would look like this:

column 1     column 2     column 3    Type Position
========     ========     ========    ==============
type 10      Type 3       Type 5      Pos 2
Type 2       Type 12      Type 8      Pos 1
Type 4       Type 3       Type 6      Pos 2
Type 1       Type 5       Type 11     Pos 1

I tried using something like below but it only works on the first IF statement - this is only for the first two columns as I build the statement up and make sure each section is working:

=IF(MATCH(F2,Sheet2!A:A,0),"Position 1",IF(MATCH(G2,Sheet2!A:A,0),"position 2","other"))

Since the first statement worked to show position 1 then thinking adding second statement would give me position 2 but it isn't. Basically I get this:

column 1     column 2     column 3    Type Position
========     ========     ========    ==============
type 10      Type 3       Type 5      
Type 2       Type 12      Type 8      Pos 1
Type 4       Type 3       Type 6      
Type 1       Type 5       Type 11     Pos 1

I can't figure out why or is there a better way of doing this in excel?

Thanks

Andrew

Upvotes: 0

Views: 364

Answers (1)

John Bustos
John Bustos

Reputation: 19574

I'm making some assumptions here:

  1. Your columns 1, 2 & 3 are in columns F, G & H.
  2. You are trying to match that data against whatever's in Sheet2, column A.

That being the case, try something like this, maybe:

=IF(ISNUMBER(MATCH(F2,Sheet2!A:A,0)),"Position 1",IF(ISNUMBER(MATCH(G2,Sheet2!A:A,0)),"position 2",IF(ISNUMBER(MATCH(H2,Sheet2!A:A,0)),"position 3")))

Hope this does the trick...

Upvotes: 2

Related Questions