IF statement nested with INDEX MATCH

[ashbot80]

I really feel like I should be able to figure this one out, but my brain is fried. I used Excel 2010...

I need to be to find the cross point between the rows and columns, then use an IF statement along with a VLOOKUP to fill in the information needed.

So I want to say, IF CO3 (column) for CA (row) is <> 0, then lookup this number (othergrid not shown), otherwise leave blank.

State</SPAN>	CO1 </SPAN>	CO2 </SPAN>	CO3 </SPAN>	CO4 </SPAN>	CO5 </SPAN>	CO6 </SPAN>	CO7 </SPAN>	CO8 </SPAN>
AK</SPAN>	159.00 </SPAN>	831.00 </SPAN>	239.00 </SPAN>	454.00 </SPAN>		421.00 </SPAN>	910.00 </SPAN>	992.00 </SPAN>
AZ</SPAN>	583.00 </SPAN>		159.00 </SPAN>	346.00 </SPAN>	655.00 </SPAN>		201.00 </SPAN>	433.00 </SPAN>
CA</SPAN>	764.00 </SPAN>	552.00 </SPAN>	25.00 </SPAN>	459.00 </SPAN>	727.00 </SPAN>	78.00 </SPAN>	400.00 </SPAN>	917.00 </SPAN>
CO</SPAN>	23.00 </SPAN>	270.00 </SPAN>	219.00 </SPAN>	566.00 </SPAN>		749.00 </SPAN>	995.00 </SPAN>	963.00 </SPAN>
CT</SPAN>		691.00 </SPAN>	854.00 </SPAN>	808.00 </SPAN>	411.00 </SPAN>	581.00 </SPAN>	455.00 </SPAN>	605.00 </SPAN>
DC</SPAN>	217.00 </SPAN>		463.00 </SPAN>		300.00 </SPAN>	603.00 </SPAN>	13.00 </SPAN>	913.00 </SPAN>
FL</SPAN>	316.00 </SPAN>	815.00 </SPAN>		130.00 </SPAN>	133.00 </SPAN>	168.00 </SPAN>		390.00 </SPAN>
HI</SPAN>		5.00 </SPAN>	929.00 </SPAN>	412.00 </SPAN>	500.00 </SPAN>	801.00 </SPAN>	691.00 </SPAN>	698.00 </SPAN>
IL</SPAN>				294.00 </SPAN>	923.00 </SPAN>	320.00 </SPAN>	751.00 </SPAN>	474.00 </SPAN>
IN</SPAN>	43.00 </SPAN>	132.00 </SPAN>	333.00 </SPAN>	266.00 </SPAN>	891.00 </SPAN>	988.00 </SPAN>	889.00 </SPAN>	607.00 </SPAN>
MA</SPAN>		445.00 </SPAN>	12.00 </SPAN>	123.00 </SPAN>		760.00 </SPAN>	423.00 </SPAN>	254.00 </SPAN>
MD</SPAN>				30.00 </SPAN>	79.00 </SPAN>	121.00 </SPAN>	572.00 </SPAN>	378.00 </SPAN>
MN</SPAN>	814.00 </SPAN>	235.00 </SPAN>	425.00 </SPAN>		229.00 </SPAN>	119.00 </SPAN>
MT</SPAN>				431.00 </SPAN>	208.00 </SPAN>	189.00 </SPAN>	732.00 </SPAN>	228.00 </SPAN>
NE</SPAN>	506.00 </SPAN>	876.00 </SPAN>		78.00 </SPAN>	872.00 </SPAN>	19.00 </SPAN>	885.00 </SPAN>	566.00 </SPAN>
NH</SPAN>	783.00 </SPAN>		456.00 </SPAN>	30.00 </SPAN>		959.00 </SPAN>	539.00 </SPAN>	779.00 </SPAN>
OR</SPAN>	444.00 </SPAN>			340.00 </SPAN>	675.00 </SPAN>	171.00 </SPAN>	433.00 </SPAN>	363.00 </SPAN>
UT</SPAN>		590.00 </SPAN>	313.00 </SPAN>	135.00 </SPAN>	526.00 </SPAN>	167.00 </SPAN>	640.00 </SPAN>	698.00 </SPAN>
VT</SPAN>	10.00 </SPAN>	125.00 </SPAN>		642.00 </SPAN>	522.00 </SPAN>	98.00 </SPAN>	204.00 </SPAN>	517.00 </SPAN>
WI</SPAN>	480.00 </SPAN>	67.00 </SPAN>	670.00 </SPAN>	702.00 </SPAN>	599.00 </SPAN>	621.00 </SPAN>	431.00 </SPAN>	702.00 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=8></COLGROUP>

 

[Snakehips]

ashbot80,

Welcome to Mr Excel.

Assuming state to find is in M1 and CO? is in L1 then try....

=IF(VLOOKUP($M$1,$A$1:$I$100,MATCH($L$1,A1:I1,0))<>0,"Do Other Lookup","")

Edit first table range (A1:I100) to suit.

Hope that helps.

 

[Jeffrey Lopez]

Welcome to the Mr. Excel Message Board!

A little more detail would be helpful. The formula you are looking at adding, where is the formula going to go? and where are you getting the look up values?

I think this is what might at least get you started in the right direction!

=IF(HLOOKUP("CO2",A1:I11,MATCH("CA",A:A,2),FALSE)<>0,"TRUE","FALSE")

If you have cell references for CO2 and CA then just replace that text, also you will need to replace the TRUE/FALSE with whatever formula you need/want!

Hope that helps!