Looking for an index match formula that looks at two different columns for a match

[RobinOfLocksley]

I have an Index match formula that is looking at sheet1, column C, and matching criteria from sheet2 column E; however, I need it to look at both E and F for matches. I'm thinking I need some kind of "and/or" nested statement, but I'm lost.

Column C (sheet1) is a combination for ID#'s and license plate numbers, column E (Master trk list linked) is license plate numbers, F(Master trk list linked) is ID#'s. If C, matches E and/or F, it should return column A(Master trk list linked) which is a truck#.

I can get the value if I only look at one column, but I need it to look at both. The formula below is what I have. Note: in addition I have a wildcard in place because sheet2's values are partial numbers; and I have an IFERROR to return another value if there is no match.

=IFERROR(INDEX('Master trk list linked'!A:A,(MATCH("*"&RIGHT(C1167,7)&"*",'Master trk list linked'!E:E,0))),"company expense")

Thanks in advance for your help.

[jason.b75]

Try

=IFERROR(INDEX('Master trk list linked'!A:A,IFERROR(MATCH("*"&RIGHT(C1167,7)&"*",'Master trk list linked'!E:E,0),MATCH("*"&RIGHT(C1167,7)&"*",'Master trk list linked'!F:F,0))),"company expense")

[RobinOfLocksley]

That worked!! Thank you so very much.

[RobinOfLocksley]

Does this formula allow me to look at a third (G) column?

[RobinOfLocksley]

Can I add a third "Match" statement to this formula to look at column (G) or is that too many arguments?

[Pete_UK]

You would need to introduce another IFERROR, like this:

=IFERROR(INDEX('Master trk list linked'!A:A,IFERROR(MATCH("*"&RIGHT(C1167,7)&"*",'Master trk list linked'!E:E,0),IFERROR(MATCH("*"&RIGHT(C1167,7)&"*",'Master trk list linked'!F:F,0),MATCH("*"&RIGHT(C1167,7)&"*",'Master trk list linked'!G:G,0)))),"company expense")

Basically, if it is not in column E try column F and if it is not there try G. If it is in none of them then return "company expense".

Hope this helps.

Pete

[RobinOfLocksley]

Yes, Pete, that worked fantastically. Thank you so very much.

[Pete_UK]

Glad to help - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Pete

[FDibbins]

Probably a better (simpler?) way would be to use a helper column to combine all those cells into 1 column, then base the MATCH on that?