Obtaining value from most recent duplicate

[teddie]

Hi All,

I have a vertical list from A1:G1

John
Fred
Alex
John
Tim
Fred
John

There are also values in B1:G1 next to those names which are irrelevant. For the second "John" I need a formula to search the vertical list above it (from bottom to top), find the first "John", and obtain the value next to it.

 

[James006]

Hello and Welcome to the Forum

You could test following formula :

=IFERROR(INDIRECT("B"&MATCH(A2,OFFSET($A$1,0,0):OFFSET(A2,-1,0),0)),"")

Hope this will help

 

[Aladin Akyurek]

I couldn't evaluate James's proposal. Hence the following.

Book1
	A	B	C	D	E
1	John	dax		fred	wax
2	Fred	wax		john	zax
3	Alex	nax
4	John	zax
5	Tim	wax
6	Fred	jax
7	John	xax
Sheet1

In E1 control+shift+enter, not just enter, and copy down:

=T(OFFSET($B$1:$B$7,ROWS($A$1:$A$7)-1,0,-SMALL(IF(T(OFFSET($A$1:$A$7,ROWS($A$1:$A$7)-1,0,-(ROW($A$1:$A$7)-ROW($A$1)+1)))=D1,ROW($A$1:$A$7)-ROW($A$1)+1),2)))

Note 1. Change the T function to the N function if the range in question is numeric.
Note 2. If so desired, you can have 2 (indicator of the second instance) in a cell of its own and substitute that cell address for 2 in the formula.

 

[teddie]

Hello and Welcome to the Forum

You could test following formula :

=IFERROR(INDIRECT("B"&MATCH(A2,OFFSET($A$1,0,0):OFFSET(A2,-1,0),0)),"")

Hope this will help

Hi James,

Thanks for the response. This formula works for the second “John” but not the third. So for the third “John” I would need it to grab what’s next to the second “John”. The fourth “John” would need to grab what’s next to the third...and so on.

 

[Aladin Akyurek]

Did you also check post #3 ?

 

[teddie]

It doesn’t correlate to my spreadsheet. You added something in D1. My spreadsheet is vertical and I need a formula to copy down when there are numerous duplicates.

 

[Aladin Akyurek]

It doesn’t correlate to my spreadsheet. You added something in D1. My spreadsheet is vertical and I need a formula to copy down when there are numerous duplicates.

I don't follow what you are trying to say. Didn't you ask

For the second "John" I need a formula to search the vertical list above it (from bottom to top), find the first "John", and obtain the value next to it.

If my understanding is not correct, care to post what the desired values are for the sample you posted?

 

[teddie]

I figured it out. Thanks all for the help.

 

[Aladin Akyurek]

I figured it out. Thanks all for the help.

That's great...