On Nov 11, 11:56*am, Pete_UK wrote:
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

On Nov 11, 3:47*pm, clk wrote:



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.- Hide quoted text -

- 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.