Searching without Vlookup shortcomings

[Ben51]

Hello everyone,

I am in sheet 2 A2.

I would like to search a row (10) from sheet 1 to match sheet 2 A1.

If i have a match,

I would like to search the row below (11) from the reference cell (i.e. AG10) for the next column encountered that contains a 5.

When i encounter a 5 in the column i would like to return the value from a particular row (column match row 449)


Thanks in advance guys!

 

[Peter_SSs]

Bit hard to follow, but does this do it?

Note that I have assumed a limit on the columns of column CZ in Sheet 1.

Excel 2010 32 bit
	A	B	C	D	E	F	G	H
10	text	text	text	abc	text	text	text	text
11		5					5
12
444
445
446							a
447							b
448							c
449							d
450							e
Sheet 1

Excel 2010 32 bit
	A
1	abc
2	d
Sheet 2

Cell Formulas
Range	Formula
A2	=INDEX('Sheet 1'!A449:CZ449,MATCH(A1,'Sheet 1'!A10:CZ10,0)+MATCH(5,INDEX('Sheet 1'!A11:CZ11,MATCH(A1,'Sheet 1'!A10:CZ10,0)+1):'Sheet 1'!CZ11,0))

 

[Ben51]

Thanks for the reply, It works perfectly!

 

[Peter_SSs]

Thanks for the reply, It works perfectly!

You are welcome. Glad it helped.