Vlookup / Hlookup iterations

[meganerema]

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!

 

[John C]

Assuming your table starts in column a and row
=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.

 

[Mike H]

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