Lookup?

[clk]

Hi. I have a worksheet (WorksheetA.xls) that calculates age of
employee and age of spouse. I have a separate worksheet
(WorksheetB.xls) that is set up like a grid. Age of participant
(employee) across the top (row 8), and age of spouse coming down
column A. I need to find age of employee on my WorksheetA.xls in cell
J20. Go to the second spreadsheet (worksheetB.xls) and go across row
8 to the age of employee. Then go back to first sheet, find age of
spouse and go down column a on worksheetb to find that age.

So for example, if on WorksheetA my employee is 41 and his spouse is
40. I need to "lookup" on WorksheetB, first employee age of 41 then
go down and find row with spouse age (40). So what I need returned is
3.3 to worksheetA.

EXAMPLE OF WORKSHEET B
Spouse Age Participant Age
40 41
42
38 2.5 2.6
2.7

39 2.9 3.0
3.1

40 3.2 3.3
3.4

I hope I am clear in my description. Thank you for any assistance.

 

[clk]

The examples shifted a bit. "column" age 42 wrapped down in the
post. Sorry if it looks confusing.

EXAMPLE OF WORKSHEET B
Spouse Age Participant Age
40 41 42
38 2.5 2.6 2.7

39 2.9 3.0 3.1

40 3.2 3.3 3.4


Trying again with less space.

 

[Pete_UK]

I started a reply and had to give lots of assumptions on where your
data was located and how much you have of it, but then you would have
to convert the formula back to suit your real data. To avoid errors in
that, please state how many rows of Spouse Ages you have in SheetB
(and what rows they occupy), and how many Employee ages you have and
what columns these use.

On SheetA I would need to know the columns used to hold Employee age
and Spouse age, and I assume from what you have said that you need a
formula to go into cell J20 of SheetA which will then be copied down
for all your employees.

Basically, you would use an INDEX / MATCH formula, but I would like to
give you a formula which reflected your actual data layout, so please
describe it more fully.

Pete

 

[clk]

I started a reply and had to give lots of assumptions on where your
data was located and how much you have of it, but then you would have
to convert the formula back to suit your real data. To avoid errors in
that, please state how many rows of Spouse Ages you have in SheetB
(and what rows they occupy), and how many Employee ages you have and
what columns these use.

On SheetA I would need to know the columns used to hold Employee age
and Spouse age, and I assume from what you have said that you need a
formula to go into cell J20 of SheetA which will then be copied down
for all your employees.

Basically, you would use an INDEX / MATCH formula, but I would like to
give you a formula which reflected your actual data layout, so please
describe it more fully.

Pete








- Show quoted text -

Hi thank you for the reply. I have been researching Index/Match and
just can't quite get it right. OK....on worksheet A. Employee Age is
located in cell J20. Spouse Age is located in P20. In cell J29 I
have a cell to show the number it should grab from Worksheet B.

On Worksheet B, I have going across row 8 (B8 through BU8). This runs
from age 19 to age 90 going across the top.

Coming down column A. Spouse's age is stored in A9 through A80.
Again this is from age 19 to age 90.

Where the two ages intersect, I need to capture that number. So first
look across to find employee age (41). Then go down column A to find
spouse's age (40). The number where to columns and rows intersect is
the number I need plugged in to cell J29 on the Worksheet A.

I hope this gives you enough detail. Please let me know if any other
information is needed. Thanks again.

 

[Pete_UK]

Here's the formula that you need in cell J29 of SheetA:

=INDEX(SheetB!$B$9:$BU$80,MATCH(P20,SheetB!$A$9:$A$80),MATCH(J20,$B
$8:$BU$8))

The first MATCH is locating the appropriate row, so it is finding a
match with the Spouse age in column A, and the second MATCH is
determining the column by finding a match with the Employee age in row
8.

Hope this helps.

Pete

 

[clk]

Here's the formula that you need in cell J29 of SheetA:

=INDEX(SheetB!$B$9:$BU$80,MATCH(P20,SheetB!$A$9:$A$80),MATCH(J20,$B
$8:$BU$8))

The first MATCH is locating the appropriate row, so it is finding a
match with the Spouse age in column A, and the second MATCH is
determining the column by finding a match with the Employee age in row
8.

Hope this helps.

Pete







- Show quoted text -

It worked!!! Thank you so much for your assistance. That is exactly
what I needed.

 

[Pete_UK]

I'm glad to hear that, so thanks for feeding back.

Actually, I was in a rush because I had to go out, and I now notice
that I missed off the reference to SheetB in the second MATCH
function. Presumably you picked this up, but just for the sake of
completeness the formula should be:

=INDEX(SheetB!$B$9:$BU$80,MATCH(P20,SheetB!$A$9:$A
$80),MATCH(J20,SheetB!$B$8:$BU$8))

Hope this helps.

Pete

 

[clk]

I'm glad to hear that, so thanks for feeding back.

Actually, I was in a rush because I had to go out, and I now notice
that I missed off the reference to SheetB in the second MATCH
function. Presumably you picked this up, but just for the sake of
completeness the formula should be:

=INDEX(SheetB!$B$9:$BU$80,MATCH(P20,SheetB!$A$9:$A
$80),MATCH(J20,SheetB!$B$8:$BU$8))

Hope this helps.

Pete



- Show quoted text -

Yes I did catch that and fixed it on my formula. Thanks again for
the assistance.