Combining 3 x INDEX MATCH

[horizonflame]

Hi All,


I have a column with INDEX/MATCH formula, but the match value could come from 1 of 3 possible columns. Could you help me combine the below into one formula please?

=IFERROR(INDEX(admin!$C$4:$C$100,MATCH(C4,admin!$B$4:$B$100,0)),"0")

=IFERROR(INDEX(admin!$E$4:$E$100,MATCH(C4,admin!$D$4:$D$100,0)),"0")

=IFERROR(INDEX(admin!$G$4:$G$100,MATCH(C4,admin!$F$4:$F$100,0)),"0")

Thanks

 

[sadath]

Hi
here is array formula =VLOOKUP(C4,INDIRECT(INDEX(A1:A3,MATCH(TRUE,COUNTIF(INDIRECT(A1:A3),C4)>0,0))),2,0)

where A1 has admin!B2:C100
A2 admin!d2:e100
A3 admin!f2:g100

 

[horizonflame]

Thanks very much at @sadath. Is there a non-array version at all to remove the need for triggering the array?
Many thanks

 

[Special-K99]

Non-array, just nest them

=IFERROR(INDEX(admin!$C$4:$C$100,MATCH(C4,admin!$B$4:$B$100,0)),IFERROR(INDEX(admin!$E$4:$E$100,MATCH(C4,admin!$D$4:$D$100,0)),IFERROR(INDEX(admin!$G$4:$G$100,MATCH(C4,admin!$F$4:$F$100,0)),0)))

 

[horizonflame]

Many thanks @Special-K99, works great!