indexing and matching

[jbrown021286]

i need to make a formula that references the dated in row 31 and when a47 matches one of the cells in row 31 it returns values from corresponding cells. for example if a47 was 2-feb b47 would need to = d42 , c47 would need to =d43 , e47 would need to =d40 and f47 would need to =d41. not sure if it makes a difference but all the cells being referenced are formula values

 

[dermie_72]

Try

=IFERROR(INDEX(42:42,MATCH(A47,31:31,0)),"")

in B47 and

=IFERROR(INDEX(43:43,MATCH(A47,31:31,0)),"")

in C47. Adjusting the index row for what row you want to return the value of.

 

[SunnyAlv]

how about this

Cell B47

=LET(data,$C$32:$P$43,
no,$B$32:$B$43,
date,$C$31:$P$31,
tch,"3+4",
HSTACK(INDEX(data,MATCH(tch,no,0),MATCH(A47,date,0)),INDEX(data,MATCH(tch,no,0)+1,MATCH(A47,date,0))))

Cell E47

=LET(data,$C$32:$P$43,
no,$B$32:$B$43,
date,$C$31:$P$31,
tch,"1+2",
HSTACK(INDEX(data,MATCH(tch,no,0),MATCH(A47,date,0)),INDEX(data,MATCH(tch,no,0)+1,MATCH(A47,date,0))))