i am wanting to create a lookup that will return a value from another sheet
based on the headers from both columns and rows...to return the value at the
intersect

for example:

column A B C
NAME UNIT HRS
JIM 1 20
TOM 3 40
RON 1 36
SALLY 2 43
TOTALS 7 139

I want to lookup the value where Unit and Totals meet... 7

the number of names in the list may vary so I dont want to set as fixed
location

also the data on that page may have more than one TOTALS row - although
column headers would be different in each set of data

for example the second set of data on that page might be

Column A B C
Name EXP BUD
JIM 10 20
TOM 23 20
RON 35 40
SALLY 40 40
Totals 108 120

What would be the most efficient formula to return the desired data?

Thanks

FYI- I have used the following formula

=VLOOKUP($A1,Sheet1!$B:$S,MATCH(C$7,Sheet1!$B4:$R4 ,0),0)

The only problem is on the vlookup for data in the second set of
information... since the number of rows can vary from time to time, I cannot
set the match for the second set of data because it will not always be the
same row...

any thoughts on how to overcome this obstacle?


"Memphus01" wrote:

i am wanting to create a lookup that will return a value from another sheet
based on the headers from both columns and rows...to return the value at the
intersect

for example:

column A B C
NAME UNIT HRS
JIM 1 20
TOM 3 40
RON 1 36
SALLY 2 43
TOTALS 7 139

I want to lookup the value where Unit and Totals meet... 7

the number of names in the list may vary so I dont want to set as fixed
location

also the data on that page may have more than one TOTALS row - although
column headers would be different in each set of data

for example the second set of data on that page might be

Column A B C
Name EXP BUD
JIM 10 20
TOM 23 20
RON 35 40
SALLY 40 40
Totals 108 120

What would be the most efficient formula to return the desired data?

Thanks