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

 

[Anthony47]

And which is your Office version?

 

[Joe4]

And which is your Office version?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[hhady]

And which is your Office version?

Office 365

 

[Anthony47]

With Office 365, try

=VSTACK(WRAPROWS(TOCOL(CN1:CN100,1),2),WRAPROWS(TOCOL(CT1:CT100,1),2))

 

[Fluff]

How about

=LET(tc,TOCOL(CN2:CT36),INDEX(tc,XMATCH(A2,tc)+1))

 

[hhady]

With Office 365, try

=VSTACK(WRAPROWS(TOCOL(CN1:CN100,1),2),WRAPROWS(TOCOL(CT1:CT100,1),2))

thanks - but my table range is (A1:AKM36) - I used the CT and CN columns as an example since they were shown in the photo uploaded.

 

[Anthony47]

Where the information you look for are in that table? In which columns, I mean

 

[hhady]

Where the information you look for are in that table? In which columns, I mean

the first column is H then N, T, Z, AF etc - So start from H and add 6 columns to get to N = (H+6) and the same pattern until column "AKM"

 

[Fluff]

Maybe

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