An alternative to nesting Ifs to get results.

[almostanexpert]

Hello All,

Thank you for your time.

I am in need of your assistance in finding a better way, a more efficient non-vba way, of getting the results I get from nesting Ifs. I currently pull data from our main frame and do a visual scan. I developed a crude If statement, =IF(D2=A2,E2,IF(F2=A2,G2,IF(H2=A2,I2,IF(J2=A2,K2,"")))), which as you can see looks at A2 and then goes to the first instance of data and checks to see if it matches, if it does not it goes to the next set of two columns (D:E, F:G, H:I etc...) and compares if the next set proves TRUE. When it does it returns the second column. So if A2 is a match with D2 then it will return what is in E2. This matching will need to occur for approximately 40 sets (80 columns) of information. So as you can see nesting that many Ifs can become a bit difficult.

I have explored Index Match but they don't seem to give me the results I would like and I have also explored SumProduct (my porsonal favorite for complex lookups) but I don't have an intersection to build my formula.

Thank you again.

 

[Joyner]

Hello, If I understand correctly, something like this should work, change the range to fit:

=INDEX(E2:L2,MATCH(A2,D2:K2,0))

If there is a possibility there is no match then:

=IFERROR(INDEX(E2:L2,MATCH(A2,D2:K2,0)),"")

 

[JoeMo]

Do both cells of a "pair" (D:E, etc) contain the same type of values (numbers or text), so that it's possible that say E2 could match A2 when none of the other data match A2. Can you post a sample of data for just the abbreviated row you refer to in your initial post (A2:I2)?

 

[almostanexpert]

Hello All,

A B C D E F G H I J K
Drew Drew 2
Drew Drew 4
Drew Drew 8

B2 = 2
B3 = 4
B4 = 8

Where the columns with out information will have information just not the one that matches column A. Sorry folks I do not have what is required to better view the sample I provided. On my first row I had put D2, Drew and E2, 2, then the next row H3, Drew and I3, 4 and the next row F3, Drew and G3, 8.

 

[almostanexpert]

@Joyner - Your solution worked, could you explain how it does please.

 

[Harry Flashman]

@Joyner - Your solution worked, could you explain how it does please.

Are you sure it works? I can see why it could work, but I can also see how it might break depending upon the values in your range A2:CE2.

Take this rough example I have put together:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
A=	Answer

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFCC99, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"]3[/TD]

</tbody>

Sheet1​

Joyner's formula correctly returns 3 (A2 = D2, thus the formula returns the value in E2).
It is able to return E2 because the reference range in the MATCH part of the formula is offset by 1 column to the right of the reference range in the INDEX part of the formula.

But what about this situation:
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
A=	Answer

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFCC99, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: right"]9[/TD]

</tbody>

​

What if A2 = E2, if that is the case Joyner's formula will return the value from F2.

But if I understand correctly you don't want to test A2 = E2. You want to test every second value
A2 = D2
A2 = F2
A2 = H2

And so on.

Thus you don't want to compare A3 to E2 (E2 only comes into it if A2 = D2).
Have I understood correctly?

 

[Harry Flashman]

If what I have assumed above is correct (and of course I might have completely misunderstood), how about a formula like this:

=IFERROR(INDEX(E2:L2,MATCH(1,(A2=D2:K2)*(MOD(COLUMN(D2:K2),2)=0),0)),"")

The above is an array formula and must be entered CTRL+SHIFT+ENTER

Note the the INDEX reference range is offset by 1 column from the the comparison range.

The above formula only matches A2 to D2, F2, H2 and so on, but it only returns values from E2, G2, I2 etc.

 

[Joyner]

almostanexpert.

Like Harry's formula, I offset the INDEX and MATCH so the formula returns the value to the right of the lookup/match value. So if the match to A2 is F2, the formula will return the value in G2.

And as Harry points out, the formula I provided is not robust and can give an incorrect value if your data is such that the lookup value in A2 can be in one of the cells you do not intend to lookup, ie. E, G, I, K, etc. If that can never be the case, the formula will work fine.

Not having seen your data, I assumed the formula would be appropriate for your data. IF it can be the case where the lookup value in A2 can be in one of the cells you do not intend to lookup, you will want to use the formula that Harry provided, as he has explained above.

 

[Harry Flashman]

Indeed. It depends on the data. If the data is arranged in such a way that there couldn't possibly be a match where you don't want there to be a match then Joyner's formula will be faster than the array formula I posted. For example if A2 contains text, and D2, F2, H2 etc all contain text, but E2, G2, I2... etc contain numbers then Joyner's formula will be fine.