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! |
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! |
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! |
All times are GMT +1. The time now is 06:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com