Match and index across multiple columns

[hhady]

Hi All,

I want your help to figure out how to search an excel sheet for a specific "task id" (in column CN, CT etc - attached photo) and return with the value one row below "task id"

so in the end i will have this result

B7CA1	0​
B7CA2	5000​
B7H1	475​
B7H2	2544​
B7H3	1170​
B7R1	29175​
B7O1	585​
B7O2	1487.5​
B10CA2	116268​
B10CA3	7200​
B10CA1	7200​
B10D1	1170​
B10H1	4250​
B10R1	214375​
B10O1	2587.5​
B10O2	13925​

so far what I figured out is to use this match function to get the (row+1) of the task ID
=(MATCH(1,MMULT(--($A$2:$AKC$36=I41),TRANSPOSE(COLUMN($A$2:$AKC$36)^0)),0)+1)

But i want your help to put it in an INDEX function or maybe there are other simpler solutions..

thanks

 

[hhady]

Maybe

=LET(tc,TOCOL(H2:AKM36),INDEX(tc,XMATCH(I41,tc)+1))

this function works however it is returning with the cell in the next column in the same Row - can you please tweak it to return with the cell in the same colum and in the Row below

Chek the below results from this function (second column) vs that i'm looking for (first column)

B7CA1	0​	0​
B7CA2	5000​	New MUA & UH system
B7H1	475​	Replace AHU 15
B7H2	2544​	0​
B7H3	1170​	Install air curtain for Main Bay doors

 

[Fluff]

How about

=LET(tc,TOCOL(H2:AKM36,,1),INDEX(tc,XMATCH(I41,tc)+1))

 

[hhady]

How about

=LET(tc,TOCOL(H2:AKM36,,1),INDEX(tc,XMATCH(I41,tc)+1))

thank you sir - this function works..

 

[Fluff]

Glad we could help & thanks for the feedback.