Index Match Across Multiple Rows

[phiphi76]

I am trying to use an index match formula but the match function will only match one column and I need it to look through 60 columns and if it finds a match return the row number and I should be able to get the index function to work.

This is the formula, the "Entry Detail" is the sheet name and looks only in column T:T, but I want it to look in column U:U and so on.

=INDEX('Entry Detail'!$CG$2:$CG$11,MATCH(B5,'Entry Detail'!T:T,0))

Thanks,

Sorry I meant across multiple columns not rows

 

[T. Valko]

I am trying to use an index match formula but the match function will only match one column and I need it to look through 60 columns and if it finds a match return the row number and I should be able to get the index function to work.

This is the formula, the "Entry Detail" is the sheet name and looks only in column T:T, but I want it to look in column U:U and so on.

=INDEX('Entry Detail'!$CG$2:$CG$11,MATCH(B5,'Entry Detail'!T:T,0))

Thanks,

Sorry I meant across multiple columns not rows

Try something like this...

Book1
	A	B	C	D
1	_	_	_	_
2	21	40	36	result1
3	44	67	58	result2
4	25	23	41	result3
5	57	15	88	result4
6	48	14	17	result5
7	_	_	_	_
8	15	result4	_	_
Sheet1

This array formula** entered in B8:

=IF(COUNTIF(A2:C6,A8),INDEX(D:D,MAX(IF(A2:C6=A8,ROW(A2:C6)))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[phiphi76]

You are amazing! I wasted so much time trying to figure this out. Thank you so much!

 

[T. Valko]

You are amazing! I wasted so much time trying to figure this out. Thank you so much!

You're welcome. Thanks for the feedback!

 

[EStrunz]

I was going to post a similar question, but this post answered it already so nicely. I hadn't used array formulas before, but this was a great opportunity to get my feet wet. Many thanks!