Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up and Compare Table Range Then
Hi guys:
I don't know if this is even possible, but, chances are that it is! I have three columns, (1) = "A" has currency values set as income, then, column (2) = "C" has a set of currency vals that are 8% of values in column "A". Then columns (3) = "E" also has currency values which are +/- 9.75% of column "A" ALL of these columns and values are on a hidden sheet called "TAX Table" Folks who fall into column (2) = "C", do due to their status. Folks who fall into column (3) = "E", do so due to their particular status also. NOW on a sheet called "EMP Data" I have 30 rows (records) of data related to various employees. I need a way to tag either "ALL" or "SOME" of those rows/records so that a "VLOOKUP" function can be performed against them matching their currency values against the Table of data on the sheet called "TAX Table" in column (1) = "A", then be able to say: "based on this persons currency value and status, they fall into group column (2) = "C" or column (3) = "E" on the "TAX Table" sheet! However, I would want the resulting values to show on the right side of the currency values on the "EMP Data" sheet. Is this possible? If so, how? I know that in Access (ALL versions) one can tag a record using a check box! Is this also possible in EXCEL? Do I need code to accomplish this, or can a VLOOKUP function do the same thing? Thanks, any help/assistance will be appreciated! Sample of data on TAX Table follows: "A" "C" "E" Income TG-1 TG-2 32100.00 2428.40 2791.10 32200.00 2442.70 2808.00 32300.00 2458.30 2824.90 32400.00 2473.90 2841.80 32500.00 2489.50 2860.00 32600.00 2505.10 2876.00 32700.00 2520.70 2893.80 Sample data from "Emp Data" sheet follows: Income 32099.76 32200.50 32289.00 32396.00 32488.79 32585.94 32697.95 The resulting values should be from Column TG-1 or TG-2 determined by status! Hope this data samples helps... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look-up and Compare Table Range Then
Believe what you need can be achieved either via VLOOKUP or INDEX/MATCH
Try Debra Dalgleish's nice coverage on VLOOKUP or INDEX/MATCH at her: http://www.contextures.com/xlFunctions02.html VLOOKUP http://www.contextures.com/xlFunctions03.html INDEX/MATCH There's also some sample workbooks available for d/l & study -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jay" wrote: Hi guys: I don't know if this is even possible, but, chances are that it is! I have three columns, (1) = "A" has currency values set as income, then, column (2) = "C" has a set of currency vals that are 8% of values in column "A". Then columns (3) = "E" also has currency values which are +/- 9.75% of column "A" ALL of these columns and values are on a hidden sheet called "TAX Table" Folks who fall into column (2) = "C", do due to their status. Folks who fall into column (3) = "E", do so due to their particular status also. NOW on a sheet called "EMP Data" I have 30 rows (records) of data related to various employees. I need a way to tag either "ALL" or "SOME" of those rows/records so that a "VLOOKUP" function can be performed against them matching their currency values against the Table of data on the sheet called "TAX Table" in column (1) = "A", then be able to say: "based on this persons currency value and status, they fall into group column (2) = "C" or column (3) = "E" on the "TAX Table" sheet! However, I would want the resulting values to show on the right side of the currency values on the "EMP Data" sheet. Is this possible? If so, how? I know that in Access (ALL versions) one can tag a record using a check box! Is this also possible in EXCEL? Do I need code to accomplish this, or can a VLOOKUP function do the same thing? Thanks, any help/assistance will be appreciated! Sample of data on TAX Table follows: "A" "C" "E" Income TG-1 TG-2 32100.00 2428.40 2791.10 32200.00 2442.70 2808.00 32300.00 2458.30 2824.90 32400.00 2473.90 2841.80 32500.00 2489.50 2860.00 32600.00 2505.10 2876.00 32700.00 2520.70 2893.80 Sample data from "Emp Data" sheet follows: Income 32099.76 32200.50 32289.00 32396.00 32488.79 32585.94 32697.95 The resulting values should be from Column TG-1 or TG-2 determined by status! Hope this data samples helps... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare to table | Excel Worksheet Functions | |||
Compare to a range of values using "from/to" | Excel Discussion (Misc queries) | |||
Compare range with another range in other workbook | Excel Worksheet Functions | |||
Need to compare 2 tables to generate 3rd table | Excel Worksheet Functions | |||
Dynamically compare two slightly different copies of a table | Excel Worksheet Functions |