Reverse lookup to find value

Hey guys, have run into a bit of a dilemma with a spreadsheet I'm working on.

What I have is a rankings sheet that uses a formula to transform a number based on some sports results, which one is greater and so on. That part works fine.

Where I've run into trouble is creating a lookup formula to determine the team playing, look up their previous ranking number and use that to determine their next rank.

H Team	H Score	vs.	A Team	A Score	H B Rank	A B Rank	H N Rank	A N Rank
PUM	98	wdb	DRA	136	1500*	1500*	1438**	1562**
DIN	74	wdb	TIM	124	1500	1500	1431	1569
KOU	97	wdb	WON	153	1500	1500	1429	1571
PUM	174	def	WON	127	last PUM no. (1438)	last WON no. (1571)
PAG	106	wdb	TIM	123	1500	last TIM no. (1569)
DIN	151	wdb	KOU	171	last DIN no. (1431)	last KOU no. (1429)

*predetermined figure to start the rankings
**generated figure off the previous H or A number

I managed to implement a backwards lookup formula but it ran into trouble determining which column the last occurrence of the name was (H Team or A Team) and which ranking to then find (H N Rank or A N Rank, respectively). If anything else needs to be clarified, I'm happy to do so.

Thanks.

Originally Posted by dilipandey

Hi eth,

welcome to the forum.

Can you update manually what result along-with a criteria which you followed to obtain that number? Thanks.


Regards,
DILIPandey
<click on below * if this helps>

Thanks.

As in place each value in manually? I can, but it's more tedious than what I'd prefer...