Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Hlookup iterations
I am trying to figure out how to do multiple iterations of vlookup or hlookup
in one cell. For example, I have the following table: Project 1 Start Date 1/10/2008 Revenue 100,000 Net Profit 20,000 Project 2 Start Date 2/10/2008 Revenue 200,000 Net Profit 30,000 Project 3 Start Date 5/10/2008 Revenue 500,000 Net Profit 40,000 I am trying to enter a formula that will find Project 2's Revenue, or 200,000. Is there a way to use vlookup (or any other function)? Any suggestions would be much appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Hlookup iterations
Assuming your table starts in column a and row 1
=VLOOKUP("Revenue",OFFSET($A$1,INDEX(MATCH(project #,$A$1:$A$10,0),1),1,3,3),2,FALSE) Obviously, expand the range if needed for more projects. The offset function is a volatile function, which means it is constantly calculated. This will cause a situation where if you open the workbook, and then close, while doing nothing else, Excel will ask you if you wish to save changes. If you search in the groups, you can find out how to deal with that through VBA. -- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "meganerema" wrote: I am trying to figure out how to do multiple iterations of vlookup or hlookup in one cell. For example, I have the following table: Project 1 Start Date 1/10/2008 Revenue 100,000 Net Profit 20,000 Project 2 Start Date 2/10/2008 Revenue 200,000 Net Profit 30,000 Project 3 Start Date 5/10/2008 Revenue 500,000 Net Profit 40,000 I am trying to enter a formula that will find Project 2's Revenue, or 200,000. Is there a way to use vlookup (or any other function)? Any suggestions would be much appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup / Hlookup iterations
Hi,
Try this assuming your data are in columns A,B & C =INDEX(C1:C9,MATCH(D1,A1:A9)+1,0) Wher D1 is the project you are looking for Mike "meganerema" wrote: I am trying to figure out how to do multiple iterations of vlookup or hlookup in one cell. For example, I have the following table: Project 1 Start Date 1/10/2008 Revenue 100,000 Net Profit 20,000 Project 2 Start Date 2/10/2008 Revenue 200,000 Net Profit 30,000 Project 3 Start Date 5/10/2008 Revenue 500,000 Net Profit 40,000 I am trying to enter a formula that will find Project 2's Revenue, or 200,000. Is there a way to use vlookup (or any other function)? Any suggestions would be much appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup - HLookup | Excel Discussion (Misc queries) | |||
hlookup & vlookup together | Excel Discussion (Misc queries) | |||
vlookup & hlookup | Excel Worksheet Functions | |||
How do I use Vlookup or Hlookup? | Excel Discussion (Misc queries) | |||
Vlookup and Hlookup | Excel Worksheet Functions |