Stuck on a lookup...

[EVANWIT84]

Hi All,

I have a question where I have about 400 rows of data. I'm trying to lookup the data where I pull the Reporting Account Name and look up it's benchmark. After looking up the benchmark I want to take the 1 Year Account Performance Less the 1 Year benchmark performance. As a result, my end result in a new column would be 0.84. I'm also trying to look this up across any date.

The 1st row is the Account
The 2nd row is it's associated benchmark.

I'm stuck here as there's no identified tying the account to the benchmark. Appreciate any pointers you would have and i'm not looking to have the problem solved. Thanks!

Reporting Account Number	Reporting Account Name	Source Account Number	Source Account Name	Account Benchmark Number	1 Year	As of Date
RUSS 1000 V	RUSS 1000 V	UMTF5	RUSS 1000 V	UMTF5	20.08​	9/30/2020​
RUSS 1000 V		IX1F	Russell 1000 Value Index	UMTF5IX1F	19.24​	9/30/2020​

 

[AlanY]

this works for your sample data

Book1
	A	B	C	D	E	F	G
1	Reporting Account Number	Reporting Account Name	Source Account Number	Source Account Name	Account Benchmark Number	1 Year	As of Date
2	RUSS 1000 V	RUSS 1000 V	UMTF5	RUSS 1000 V	UMTF5	20.08	9/30/2020
3	RUSS 1000 V		IX1F	Russell 1000 Value Index	UMTF5IX1F	19.24	9/30/2020
4
5
6
7	RUSS 1000 V	0.84
Sheet1

Cell Formulas
Range	Formula
B7	B7	=OFFSET($A$1,MATCH(A7,A:A,0)-1,5)-OFFSET($A$1,MATCH(A7,A:A,0),5)

 

[EVANWIT84]

This is great, I wasn't thinking of using Offset with 0 to offset the rows. Thank you for your help!

 

[AlanY]

you're welcome