Dynamic VLOOKUP

[smitpau]

Hi,

Wonder if it is possible to have a formula that dynamically looks up values from a table as the reference cell instead of manually typing in each one.
Once it finds a new VLOOKUP value it would use that by default until it finds the next value in the table and so on.
In the example below it would be column B that instead of manually inputting Dummy 1, Dummy 2 etc it would match Dummy 2 then use that as the lookup value, see Dummy 3 then use that etc.

Hope that I've explained that properly.

Thanks,

Book1
	B	C	D	E	F
2	Include	P&L
3	Yes	Dummy 1		Company	Include
4	Yes	Revenue		Dummy 1	Yes
5	Yes	COS		Dummy 2	Yes
6	Yes	Profit		Dummy 3	No
7
8	Yes	Dummy 2
9	Yes	Revenue
10	Yes	COS
11	Yes	Profit
12
13	No	Dummy 3
14	No	Revenue
15	No	COS
16	No	Profit
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=VLOOKUP($C$3,$E$4:$F$6,2,FALSE)
B8:B11	B8	=VLOOKUP($C$8,$E$4:$F$6,2,FALSE)
B13:B16	B13	=VLOOKUP($C$13,$E$4:$F$6,2,FALSE)

 

[snjpverma]

The column C already reflects the LOOKUP value i.e. Dummy1, Dummy2 or Dummy 3. Then how is it a dynamic lookup?
Am I missing something?
Could you given an example of the Dynamic one?

 

[smitpau]

I should have put a question mark in the title.

It's basically instead of hardcoding the VLOOKUP to search for Dummy 1, Dummy 2 etc each time, I wonder if there is a way it could automatically change that, as for example once it sees column C says Dummy 2 that would be the new lookup value until it sees Dummy 3.

Hope that helps.