Formula that would pick the last value in a column depending on the value in the next column

[Dudi Shvejk]

Hi everyone!
I need your help with the following issue.
I need a formula that would insert into a cell the last value from the first column depending on the wording in the second column. In other words, I need a formula that would insert into one cell the value of 40 (which is the last value with "football") and into another cell it should insert 456 (which is the last value for "tennis").
Thank you!
[TABLE="width: 500"]
<tbody>[TR]
[TD]234[/TD]
[TD]tennis[/TD]
[/TR]
[TR]
[TD]4234[/TD]
[TD]football[/TD]
[/TR]
[TR]
[TD]546[/TD]
[TD]tennis[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]tennis[/TD]
[/TR]
[TR]
[TD]456456[/TD]
[TD]football[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]football[/TD]
[/TR]
[TR]
[TD]54645[/TD]
[TD]tennis[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]tennis[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Re: Help with the formula that would pick the last value in a column depending on the value in the next column

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	234	tennis		tennis	456
2	4234	football
3	546	tennis
4	4	tennis
5	456456	football
6	40	football
7	56645	tennis
8	456	tennis
Sheet1

Cell Formulas
Range	Formula
E1	=LOOKUP(2,1/($B$1:$B$8=$D$1),$A$1:$A$8)

WBD

 

[arthurbr]

Re: Help with the formula that would pick the last value in a column depending on the value in the next column

Perhaps something like = lookup(2,1/(b1:b10="football"),a1:a10)
Same for tennis
Adapt range as needed

 

[Dudi Shvejk]

Re: Help with the formula that would pick the last value in a column depending on the value in the next column

Thank you very much!

 

[Dudi Shvejk]

Re: Help with the formula that would pick the last value in a column depending on the value in the next column

Thank you!

 

[Mike Szczesny]

Re: Help with the formula that would pick the last value in a column depending on the value in the next column

Can you something like this for the last text entry? =index($B$1:$B$8,match("Ω",$B$1:$B$8)) Use Alt+234 to produce Ω