Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If or index formula help
I have two sheets of data:
Rating Transaction Amount New (Y/N field) Approver? 6 10,000 y based on the three entries I want to go to my lookup table in sheet 2 and find what approval level is required meeting the criteria and enter it i nthe approver field in sheet A. So for the scnerio above column A = 6 column B = 10,000 , column C = Y and I want to find out who needs to approve by looking at the look up table created on sheet 2. Sheet 2 is set up: Approver rating transaction New Associate 6 5000. y General Mgr 6 15000. y Executive 6 75000. y President 6 over 75K y the look up table is chrnological the greater the transaction amoutn the greater the level of approval. In the above case on sheet 1 the formula should return the value in A2 which is General Mgr because the rating was 6 the transaction amount of 10000 was greater then the 5000 but less then or equal to the 15000. the lookup table has other ratings and values this is just an example. What formula should I use to get the right approval from the lookup table from sheet A into column D in sheet 1 based on the criteria? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If or index formula help
You need to change this:
over 75K To a really big number. I would use 1E100 (which is scientific notation for 1 followed by 100 zeros) Try this array formula** : =INDEX(Sheet2!A2:A5,MATCH(1,(Sheet2!B2:B5=A2)*(B2< =Sheet2!C2:C5)*(Sheet2!D2:D5=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Belinda7237" wrote in message ... I have two sheets of data: Rating Transaction Amount New (Y/N field) Approver? 6 10,000 y based on the three entries I want to go to my lookup table in sheet 2 and find what approval level is required meeting the criteria and enter it i nthe approver field in sheet A. So for the scnerio above column A = 6 column B = 10,000 , column C = Y and I want to find out who needs to approve by looking at the look up table created on sheet 2. Sheet 2 is set up: Approver rating transaction New Associate 6 5000. y General Mgr 6 15000. y Executive 6 75000. y President 6 over 75K y the look up table is chrnological the greater the transaction amoutn the greater the level of approval. In the above case on sheet 1 the formula should return the value in A2 which is General Mgr because the rating was 6 the transaction amount of 10000 was greater then the 5000 but less then or equal to the 15000. the lookup table has other ratings and values this is just an example. What formula should I use to get the right approval from the lookup table from sheet A into column D in sheet 1 based on the criteria? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If or index formula help
Hi Berlinda
I have post the solution at your other similar post. pls check -- If this posting was helpful, please click on the Yes button Thank You cheers, "Belinda7237" wrote: I have two sheets of data: Rating Transaction Amount New (Y/N field) Approver? 6 10,000 y based on the three entries I want to go to my lookup table in sheet 2 and find what approval level is required meeting the criteria and enter it i nthe approver field in sheet A. So for the scnerio above column A = 6 column B = 10,000 , column C = Y and I want to find out who needs to approve by looking at the look up table created on sheet 2. Sheet 2 is set up: Approver rating transaction New Associate 6 5000. y General Mgr 6 15000. y Executive 6 75000. y President 6 over 75K y the look up table is chrnological the greater the transaction amoutn the greater the level of approval. In the above case on sheet 1 the formula should return the value in A2 which is General Mgr because the rating was 6 the transaction amount of 10000 was greater then the 5000 but less then or equal to the 15000. the lookup table has other ratings and values this is just an example. What formula should I use to get the right approval from the lookup table from sheet A into column D in sheet 1 based on the criteria? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index formula trouble | Excel Discussion (Misc queries) | |||
INDEX MATCH formula | Excel Worksheet Functions | |||
Index formula help | Excel Discussion (Misc queries) | |||
index match formula | Excel Worksheet Functions | |||
Sum and Index formula? | Excel Discussion (Misc queries) |