INDEX MATCH One Result, But Look In Several Columns / Matrix of Info

Hi All -

I am trying to put together a formula where I can perform a standard index/match, BUT owing to prehistoric layout of existing information, I need the formula to index from 8 columns for the one result it needs, rather than one column as normal.

I know you can index match columns and rows when they are named, but these aren't as such, and the rub is that the correct result can appear in any of the 8 columns at any time, so it just needs to 'hunt for the exact result and then match as normal.

I've attached a mock-up that hopefully shows what I mean.

Thanks,

Stuart

the first result would be simplest with something like:

=IFERROR(VLOOKUP(A1,F2:G5,2,0),IFERROR(VLOOKUP(A1,H2:I5,2,0),VLOOKUP(A1,J2:K5,2,0)))

for the Alt2 value you can use something like:

=INDEX(M:M,AGGREGATE(15,6,ROW(F2:J5)/(F2:J5=A1),1))

modify ranges to suit but try to avoid entire column references for the AGGREGATE, if you can, particularly if you have lots of these calcs in your file (in Col C)

Option one works a treat, thanks! I have to grab the information from another workbook, so although the formula is very long, it is still simpler and a quicker working calculation than what I had in mind as a work-around. Thanks very much