Offsetting the final result in a two column search.

[Donkson]

Hi, not sure if this particular issue can be solved or not. I've looked around and seen close to the same problem but not quite.

apple	banana	1	2
carrot	orange	3	4
banana	carrot	5	6
grape	orange	7	8
banana	grape	9	12
orange	apple	13	20

So for the above table example, what I need to be able to do is lookup a fruit, and have it return the last occurrence two cells to the right.

For example, looking up orange will return 13. Apple will return 20, grape 12 and so on.

Would greatly appreciate any assistance with this.

 

[Joe4]

If that data range you posted was in the range A1:D6, this formula would return the result you want:

=VLOOKUP("orange",A1:C6,3,0)

Is that what you are looking for?

 

[Donkson]

Nope it’s not that simple because that returns the first value, and the corresponding result isn’t always the third column.

If the bottom result is in column A, I need it to return column C. If it’s column B I need it to return column D.

 

[Marcelo Branco]

Try

Pasta1
	A	B	C	D	E	F	G	H
1	Fruit	Fruit	Value1	Value2		Fruit	Row of Last Occurence	Result
2	apple	banana	1	2		apple	6	20
3	carrot	orange	3	4		banana	5	9
4	banana	carrot	5	6		carrot	3	6
5	grape	orange	7	8		grape	5	12
6	banana	grape	9	12		orange	6	13
7	orange	apple	13	20
8
Plan3

Cell Formulas
Range	Formula
G2:G6	G2	=AGGREGATE(14,6,(ROW(A$2:B$7)-ROW(A$2)+1)/(A$2:B$7=F2),1)
H2:H6	H2	=INDEX(C$2:D$7,G2,MATCH(F2,INDEX(A$2:B$7,G2,0),0))

M.

 

[jtakw]

Hi,

Without helper column, I came up with this:

Book3.xlsx
	A	B	C	D	E	F	G
1	apple	banana	1	2		orange	13
2	carrot	orange	3	4		apple	20
3	banana	carrot	5	6		grape	12
4	grape	orange	7	8
5	banana	grape	9	12
6	orange	apple	13	20
Sheet940

Cell Formulas
Range	Formula
G1:G3	G1	=LOOKUP(2,1/SEARCH(F1,A$1:A$6&B$1:B$6),IF(LOOKUP(2,1/(A$1:A$6=F1),ROW(A$1:A$6))>LOOKUP(2,1/(B$1:B$6=F1),ROW(B$1:B$6)),C$1:C$6,D$1:D$6))

 

[bebo021999]

With A1:B6 is fruit, C1:D6 is value; F1 is lookup fruit

=INDEX($A$1:$D$6,AGGREGATE(14,6,ROW($A$1:$B$6)/($A$1:$B$6=F1),1),MATCH(F1,INDEX($A$1:$D$6,AGGREGATE(14,6,ROW($A$1:$B$6)/($A$1:$B$6=F1),1),),0)+2)

 

[Marcelo Branco]

Without helper column

I used a helper column just to simplify the formulas - not strictly necessary. It's possible to replace all the instances of G2 in H2 formula by the formula in G2.

M.

 

[jtakw]

I used a helper column just to simplify the formulas - not strictly necessary. It's possible to replace all the instances of G2 in H2 formula by the formula in G2.

M.

I absolutely know that Marcelo, I was also thinking of a helper column, but since you already provided a solution with such, I put together what I posted, it's not the prettiest, but hope it works also.

 

[Donkson]

Thanks guys that does exactly what I was after.

Greatly appreciated.

 

[jtakw]

You're welcome, glad we can help.