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.

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

 

[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!