Looking up several sets of data/several tables with xlookup (not multiple variables) in excel

Question

I'm struggling to word this question specifically enough to find an answer by searching.

I have a spreadsheet that looks a bit like this:

I'm trying to write a XLOOKUP that searches through columns A and C and E to find matching values in the cells to their right in columns B and D and F. It currently looks incredibly messy as my current solution is to nest each set in an 'If not found':

=XLOOKUP(value,A:A,B:B,XLOOKUP(value,C:C,D:D,XLOOKUP(value,E:E,F:F,"")))

Which will get increasingly annoying as it's very likely that I'll need even more sets. Is there a less messy way to refer to all the lookup array columns and all the return array columns? & of course means multiple variables, + doesn't work, and having overlapping dynamic ranges (A:E,B:F) freaks it out.

Thanks x

question from:https://stackoverflow.com/questions/65902501/looking-up-several-sets-of-data-several-tables-with-xlookup-not-multiple-variab

Answer 1

Waitting for answers