If with multiple criteria

[raccoon588]

Id like to write a formula that has an if with more then one criteria but nothing seems to be working. =if(n1="ablation", i want to look in a table for two criteria and return the amount in column H.

 

[joshman108]

=if(and(n1="ablation",othercritera),h1,"")

This will work. Change othercritea to be whatever you want

 

[JoeMo]

You can use AND or OR depending on whether both criteria must be met, or just one of the two, respectively.

=IF(AND(n1="ablation",o1 = "something else"),H1,"")

 

[raccoon588]

I think i need to use a vlookup nested inside an IF. can you have multiple criteria for a vlookup?

 

[joshman108]

Not to my knowledge but you can for Match, inside of index/match like this:

=INDEX(MATCH(CELL1&CELL2,RANGE1&RANGE2,0))

if you give data we can help write the formula so it works, if you need help

 

[raccoon588]

[TABLE="width: 1167"]
<colgroup><col><col><col><col><col><col><col><col><col span="5"><col><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]Deck[/TD]
[TD]Resource Name[/TD]
[TD]OutputSum[/TD]
[TD]GoalSum[/TD]
[TD]Goal%[/TD]
[TD]TheoSum[/TD]
[TD]OEE%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MASTER[/TD]
[TD="colspan: 2"]Ablation[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2019[/TD]
[TD]HIL-9007[/TD]
[TD]HIL-9007-FIM[/TD]
[TD]380441[/TD]
[TD]423262[/TD]
[TD]89.88%[/TD]
[TD]536131[/TD]
[TD]70.96%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]OEE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2019[/TD]
[TD]HIL-Ablation[/TD]
[TD]HIL-Ablation-Laser[/TD]
[TD]173949[/TD]
[TD]308741[/TD]
[TD]56.34%[/TD]
[TD]353882[/TD]
[TD]49.15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10/1/2019[/TD]
[TD]HIL-9007-FIM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2019[/TD]
[TD]HIL-Coating[/TD]
[TD]HIL-Coating-QRQC[/TD]
[TD]635222[/TD]
[TD]741375[/TD]
[TD]85.68%[/TD]
[TD]892736[/TD]
[TD]71.15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2019[/TD]
[TD]HIL-H13Assembly[/TD]
[TD]HIL-H13Assembly-FIM[/TD]
[TD]391167[/TD]
[TD]537350[/TD]
[TD]72.80%[/TD]
[TD]661274[/TD]
[TD]59.15%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dec[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2019[/TD]
[TD]HIL-H13Capsule[/TD]
[TD]HIL-H13Capsule-RingMo[/TD]
[TD]465458[/TD]
[TD]413297[/TD]
[TD]112.62%[/TD]
[TD]514479[/TD]
[TD]90.47%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2019[/TD]
[TD]HIL-H8[/TD]
[TD]HIL-H8-Ager[/TD]
[TD]425523[/TD]
[TD]529793[/TD]
[TD]80.32%[/TD]
[TD]721731[/TD]
[TD]58.96%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Aug[/TD]
[TD]#ref ![/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sep[/TD]
[TD]#ref ![/TD]
[TD]





[/TD]
[/TR]
</tbody>[/TABLE]
This is some of my data. Will be adding months as they close. over to the right i would like to be able to change the data based on the machine. ablation is an example of one of the machines i need the OEE % number to show in the cell that has the #ref .