| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Does anyone know how to create a lookup that will do this?
I have a table that looks like the following example.
Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I dont have to type it in by hand every month. For example I may need to have a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
Hi,
To VLOOKUP you may need the values to be unique value in the column. Here I can't find any unique value. If you have a unique value I can give you the VLOOKUP function for the below Thanks, Shail Mascot wrote: I have a table that looks like the following example. Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I don't have to type it in by hand every month. For example I may need to have a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
You have more responses at your other posts.
Mascot wrote: I have a table that looks like the following example. Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I dont have to type it in by hand every month. For example I may need to have a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Does anyone know how to create a lookup that will do this?
Hi again Mascot,
1. Suppose you have the data on your spreadsheet from A1 to H21 2. On A26 you enter the desire date, on B25 desired LOC and on C25 the desired ACCT 3. Suppose you filled May-06, M101 and 1001 respectively on the cells as mentioned above. 4. to get the "Beg BAL", put down the function on A28 =INDEX(D2:D21,MATCH(A26&B26&C26,A2:A21&B2:B21&C2:C 21,0)) so as for "Activity" put down the function on B28 =INDEX(E2:E21,MATCH(A26&B26&C26,A2:A21&B2:B21&C2:C 21,0)) and for "Ending" put the function on C28 =INDEX(F2:F21,MATCH(A26&B26&C26,A2:A21&B2:B21&C2:C 21,0)) Remember, these are ARRAY functions so you need to press Alt+Ctrl+Enter, instead of just a simple enter to execute. You will get a brace {} around your function if you did it correct and ofcourse the desired numbers. Goodluck!!! Hope this will work for you. Thanks Shail Mascot wrote: I have a table that looks like the following example. Date LOC ACCT Beg Bal Activity Ending May-06 M101 1001 10.00 210.00 310.00 May-06 M101 1002 20.00 220.00 320.00 May-06 M101 1003 30.00 230.00 330.00 May-06 M101 1004 40.00 240.00 340.00 May-06 M101 1005 50.00 250.00 350.00 Jun-06 M101 1001 60.00 260.00 360.00 Jun-06 M101 1002 70.00 270.00 370.00 Jun-06 M101 1003 80.00 280.00 380.00 Jun-06 M101 1004 90.00 290.00 390.00 Jun-06 M101 1005 100.00 300.00 400.00 May-06 M102 1001 110.00 310.00 410.00 May-06 M102 1002 120.00 320.00 420.00 May-06 M102 1003 130.00 330.00 430.00 May-06 M102 1004 140.00 340.00 440.00 May-06 M102 1005 150.00 350.00 450.00 Jun-06 M102 1001 160.00 360.00 460.00 Jun-06 M102 1002 170.00 370.00 470.00 Jun-06 M102 1003 180.00 380.00 480.00 Jun-06 M102 1004 190.00 390.00 490.00 Jun-06 M102 1005 200.00 400.00 500.00 I have a spreadsheet that I need to fill out every month in a specific format so I need to have something that will pull info from this table so I don't have to type it in by hand every month. For example I may need to have a specific DATE, LOC, ACCT and then the amount in one of the three columns (Beg Bal, Activity, Ending Balance). Oh also the DATE may be because it will change every month so I should probably link it to a date4 cell. Let me know if any of you have any ideas. Thanks Mascot |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create Lookup Table | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions | |||
| how do i create a lookup | Excel Discussion (Misc queries) | |||
Linear Mode
