| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP, MATCH, INDEX HELP!
Here's what I'm looking at:
http://img148.imageshack.us/my.php?i...ventorymv7.jpg The left image is the summary page. I need a formula in the "Days Out" column that will look up data in the inventory sheet (right image) and pull the most current 'actual' inventory count and divide it by the average daily sales. Any ideas? Someone gave me a good LOOKUP formula, but it only worked horizontally and I need this to be vertical. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, MATCH, INDEX HELP!
Ok...
So you need the last number that corresponds to "actual" in column C. If that's the case, based on the screencaps, the last number that corresponds to "actual" is 0 in cell D22. Am I on the right track? -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Here's what I'm looking at: http://img148.imageshack.us/my.php?i...ventorymv7.jpg The left image is the summary page. I need a formula in the "Days Out" column that will look up data in the inventory sheet (right image) and pull the most current 'actual' inventory count and divide it by the average daily sales. Any ideas? Someone gave me a good LOOKUP formula, but it only worked horizontally and I need this to be vertical. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, MATCH, INDEX HELP!
You're back! Great!
I accidentally hit the "Question Answered" button on the previous thread so I didn't think it would get looked at again. You're exactly right. I purposely moved the 'actual' row to the bottom of each date so it would always be the last entry. But yes, that is the number I'm going after and right now D22 is what I would like it to pull up. All the data in there is garbage obviously. Thanks! "T. Valko" wrote: Ok... So you need the last number that corresponds to "actual" in column C. If that's the case, based on the screencaps, the last number that corresponds to "actual" is 0 in cell D22. Am I on the right track? -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Here's what I'm looking at: http://img148.imageshack.us/my.php?i...ventorymv7.jpg The left image is the summary page. I need a formula in the "Days Out" column that will look up data in the inventory sheet (right image) and pull the most current 'actual' inventory count and divide it by the average daily sales. Any ideas? Someone gave me a good LOOKUP formula, but it only worked horizontally and I need this to be vertical. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, MATCH, INDEX HELP!
If my hunch is correct then this should work.
"Actual" will always be the last entry of the column. Entered in C3 and copied down: =LOOKUP(1E+100,INDEX(Sheet1!D$2:H$22,,MATCH(A3,She et1!D$1:H$1,0))) Adjust ranges and sheet name to suit. -- Biff Microsoft Excel MVP "igotboost" wrote in message ... You're back! Great! I accidentally hit the "Question Answered" button on the previous thread so I didn't think it would get looked at again. You're exactly right. I purposely moved the 'actual' row to the bottom of each date so it would always be the last entry. But yes, that is the number I'm going after and right now D22 is what I would like it to pull up. All the data in there is garbage obviously. Thanks! "T. Valko" wrote: Ok... So you need the last number that corresponds to "actual" in column C. If that's the case, based on the screencaps, the last number that corresponds to "actual" is 0 in cell D22. Am I on the right track? -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Here's what I'm looking at: http://img148.imageshack.us/my.php?i...ventorymv7.jpg The left image is the summary page. I need a formula in the "Days Out" column that will look up data in the inventory sheet (right image) and pull the most current 'actual' inventory count and divide it by the average daily sales. Any ideas? Someone gave me a good LOOKUP formula, but it only worked horizontally and I need this to be vertical. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, MATCH, INDEX HELP!
A few tweaks to the arrays and it worked perfectly!
Thanks so much! "T. Valko" wrote: If my hunch is correct then this should work. "Actual" will always be the last entry of the column. Entered in C3 and copied down: =LOOKUP(1E+100,INDEX(Sheet1!D$2:H$22,,MATCH(A3,She et1!D$1:H$1,0))) Adjust ranges and sheet name to suit. -- Biff Microsoft Excel MVP "igotboost" wrote in message ... You're back! Great! I accidentally hit the "Question Answered" button on the previous thread so I didn't think it would get looked at again. You're exactly right. I purposely moved the 'actual' row to the bottom of each date so it would always be the last entry. But yes, that is the number I'm going after and right now D22 is what I would like it to pull up. All the data in there is garbage obviously. Thanks! "T. Valko" wrote: Ok... So you need the last number that corresponds to "actual" in column C. If that's the case, based on the screencaps, the last number that corresponds to "actual" is 0 in cell D22. Am I on the right track? -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Here's what I'm looking at: http://img148.imageshack.us/my.php?i...ventorymv7.jpg The left image is the summary page. I need a formula in the "Days Out" column that will look up data in the inventory sheet (right image) and pull the most current 'actual' inventory count and divide it by the average daily sales. Any ideas? Someone gave me a good LOOKUP formula, but it only worked horizontally and I need this to be vertical. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, MATCH, INDEX HELP!
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "igotboost" wrote in message ... A few tweaks to the arrays and it worked perfectly! Thanks so much! "T. Valko" wrote: If my hunch is correct then this should work. "Actual" will always be the last entry of the column. Entered in C3 and copied down: =LOOKUP(1E+100,INDEX(Sheet1!D$2:H$22,,MATCH(A3,She et1!D$1:H$1,0))) Adjust ranges and sheet name to suit. -- Biff Microsoft Excel MVP "igotboost" wrote in message ... You're back! Great! I accidentally hit the "Question Answered" button on the previous thread so I didn't think it would get looked at again. You're exactly right. I purposely moved the 'actual' row to the bottom of each date so it would always be the last entry. But yes, that is the number I'm going after and right now D22 is what I would like it to pull up. All the data in there is garbage obviously. Thanks! "T. Valko" wrote: Ok... So you need the last number that corresponds to "actual" in column C. If that's the case, based on the screencaps, the last number that corresponds to "actual" is 0 in cell D22. Am I on the right track? -- Biff Microsoft Excel MVP "igotboost" wrote in message ... Here's what I'm looking at: http://img148.imageshack.us/my.php?i...ventorymv7.jpg The left image is the summary page. I need a formula in the "Days Out" column that will look up data in the inventory sheet (right image) and pull the most current 'actual' inventory count and divide it by the average daily sales. Any ideas? Someone gave me a good LOOKUP formula, but it only worked horizontally and I need this to be vertical. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup or Index/Match | Excel Discussion (Misc queries) | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
| vlookup, match, index: all some or one? | Excel Discussion (Misc queries) | |||
| Vlookup or Index/Match | Excel Discussion (Misc queries) | |||
| vlookup, match/index ???? | Excel Worksheet Functions | |||
Linear Mode
