Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
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. On Nov 11, 10:47*am, 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
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 On Nov 11, 6:07*pm, clk wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
On Nov 11, 1:32*pm, Pete_UK wrote:
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 On Nov 11, 6:07*pm, clk wrote: 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.- Hide quoted text - - Show quoted text - It worked!!! Thank you so much for your assistance. That is exactly what I needed. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
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 On Nov 11, 7:39*pm, clk wrote: It worked!!! *Thank you so much for your assistance. *That is exactly what I needed. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup?
On Nov 11, 4:10*pm, Pete_UK wrote:
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 On Nov 11, 7:39*pm, clk wrote: It worked!!! *Thank you so much for your assistance. *That is exactly what I needed.- Hide quoted text - - Show quoted text - Yes I did catch that and fixed it on my formula. :) Thanks again for the assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vertical lookup with a lookup value that returns multiple matches | Excel Programming | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions |