Can I use OR within a MATCH function?

[mjhokie13]

I am trying to make a counter using a match function. The below formula is what I was using, and it works fine, but I am needing the MATCH to look for multiple arguments. I tried an OR function but it returns an error.

=IFERROR(MATCH("X",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),"")

I tried an OR function, as shown below, but it doesn't work. How do I fix this?

=IFERROR(MATCH(OR("X","Y","Z"),OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),"")

 

[baitmaster]

welcome to the board

This fails because OR("X","Y","Z") will be evaluated by itself and return a #value! error. OR is a logical formula that returns TRUE or FALSE, it doesn't allow you to look for X or Y or Z. You'll probably have to look for each value individually, and then return the minimum value

Sadly this would make your formula
=MIN(IFERROR(MATCH("X",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),""),IFERROR(MATCH("Y",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),""),IFERROR(MATCH("Z",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),""))

 

[mjhokie13]

Ahh, that makes sense. Will MIN work if my lookup value in the MATCH function is a text item? I assumed MIN only worked with numeric values.

welcome to the board

This fails because OR("X","Y","Z") will be evaluated by itself and return a #value! error. OR is a logical formula that returns TRUE or FALSE, it doesn't allow you to look for X or Y or Z. You'll probably have to look for each value individually, and then return the minimum value

Sadly this would make your formula
=MIN(IFERROR(MATCH("X",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),""),IFERROR(MATCH("Y",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),""),IFERROR(MATCH("Z",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),""))

 

[Tetra201]

I am trying to make a counter using a match function. The below formula is what I was using, and it works fine, but I am needing the MATCH to look for multiple arguments. I tried an OR function but it returns an error.

=IFERROR(MATCH("X",OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),"")

I tried an OR function, as shown below, but it doesn't work. How do I fix this?

=IFERROR(MATCH(OR("X","Y","Z"),OFFSET('Unit Data'!$C$2,0,$A$8-1,COUNTA('Unit Data'!$C:$C),1),0),"")

What are you trying to achieve? -- To see if any/all of "X","Y","Z" are present in your range?

 

[baitmaster]

Agree with Tetra, something doesn't add up here, so what are you actually trying to do?

MATCH will return the location within a range that contains the value you're looking for - so it will return 1 if found in the first cell, 2 if in the second etc. It won't return a text item. Therefore MIN works in this case - but if you're expecting a text answer then you're asking the wrong question