MATCH function where the search term varies

Hi,

I have to use the match function to identify rows listing revenue data. Each company has it's own excel file and the revenue rows are in different places. The problem is that some companies call it "revenues" and others call it "operating revenue". Is there a way to write a formula that'll use the match function to search for "revenues" but if nothing comes up it'll cycle back through and search for "operating revenue"? I tried implanting an OR("revenues","operating revenue") into the MATCH function but then the formula wouldn't work properly. Thanks a lot.

Joey

Assuming that Column A contains your data...

B1, copied down:

=IF(ISNUMBER(MATCH(A1,{"revenues","operating revenue"},0)),"revenue item","non-revenue item")

OR

=IF(ISNUMBER(MATCH(A1,$C$1:$C$2,0)),"revenue item","non-revenue item")

...where C1:C2 contains "revenues" and "operating revenue". You can add to this list, if need be. If so, adjust the reference accordingly.

Hope this helps!

Originally Posted by jbortfeld

Hi,

I have to use the match function to identify rows listing revenue data. Each company has it's own excel file and the revenue rows are in different places. The problem is that some companies call it "revenues" and others call it "operating revenue". Is there a way to write a formula that'll use the match function to search for "revenues" but if nothing comes up it'll cycle back through and search for "operating revenue"? I tried implanting an OR("revenues","operating revenue") into the MATCH function but then the formula wouldn't work properly. Thanks a lot.

Joey