Lookup formula

[JV0710]

Hi, Please help me with this lookup query.

I have a column of data in a hierarchy (columnA).

As per the picture posted: if I type the store in cell D3, I want a formula in E3 to return the area that the store belongs in.

Thank you
JVN

Book1
	A	B	C	D	E	F	G	H	I	J
1		%Growth		Store	Area
2	Area1	12
3	store1	10		store6	Area2
4	store2	14
5	store3	12		needaformulainE3thatwillreturntheareathatstore6(F3)belongsin
6	Area2	14
7	store4	10
8	store5	10
9	store6	15
10	store7	16
11	store8	4
12	Area3	9
13	store9	6
14	store10	6
15	store11	12
16	store12	8
Sheet1

 

[Aladin Akyurek]

E3:

=LOOKUP(2,1/(LEFT(OFFSET(A2,0,0,MATCH(D3,$A$2:$A$16,0)),4)="Area"),$A$2:$A$16)

 

[JV0710]

Thank you Aladin

This works great. . .

I would like to add a complication though . . .

If I am not able to specify the name of the "parent" i.e. Area, then how will i determine the answer. for example I may have Region, area, Store and product . . . example:

Area lookup query.xls
	A	B	C	D	E	F	G	H
1		%Growth		Store	Area
2	Region1	12.5
3	Area1	12
4	store1	10		prod1	store5
5	store2	14
6	store3	12		needaformulainE3thatwillreturntheparentof(F3)
7	Area2	14
8	store4	10
9	store5	10
10	prod1	7
11	prod2	13
12	store6	15
13	store7	16
14	store8	4
15	Region2	9
16	Area3	9
17	store9	6
18	store10	6
19	store11	12
20	store12	8
Sheet1

 

[JV0710]

I'm sorry . . . That should read " the parent of (D3)"

 

[Aladin Akyurek]

I'm sorry . . . That should read " the parent of (D3)"

What would be the parent of prod2?

BTW, your data representation is not optimal in that it requires expensive processing...

 

[JV0710]

The parent of prod2 would also be store5.

The data presentation is from an export into excel from reporting system. I am trying to do conditional formatting based on comparing performance of the Area, store, etc to it's parent performance.

for example: prod1 % growth is lower than the parent (Store) % growth and prod2 % growth is higher

 

[Aladin Akyurek]

The parent of prod2 would also be store5.

The data presentation is from an export into excel from reporting system. I am trying to do conditional formatting based on comparing performance of the Area, store, etc to it's parent performance.

for example: prod1 % growth is lower than the parent (Store) % growth and prod2 % growth is higher

=LOOKUP(2,1/(LEFT(OFFSET(A2,0,0,MATCH(D4,$A$2:$A$20,0)),5)="Store"),$A$2:$A$20)

 

[JV0710]

Thanks Aladin

I think I can work with this

I have joined the two formulae that you gave above and it works if I enter "prod*" in cell D4, but I get #N/A if I enter "store*"

=IF(LEFT(D4,5)="Store",LOOKUP(2,1/(LEFT(OFFSET(A2,0,0,MATCH(D3,$A$2:$A$50,0)),4)="Area"),$A$2:$A$50),IF(LEFT(D4,4)="prod",LOOKUP(2,1/(LEFT(OFFSET(A2,0,0,MATCH(D4,$A$2:$A$50,0)),5)="Store"),$A$2:$A$50),""))

 

[Aladin Akyurek]

Thanks Aladin

I think I can work with this

I have joined the two formulae that you gave above and it works if I enter "prod*" in cell D4, but I get #N/A if I enter "store*"

=IF(LEFT(D4,5)="Store",LOOKUP(2,1/(LEFT(OFFSET(A2,0,0,MATCH(D3,$A$2:$A$50,0)),4)="Area"),$A$2:$A$50),IF(LEFT(D4,4)="prod",LOOKUP(2,1/(LEFT(OFFSET(A2,0,0,MATCH(D4,$A$2:$A$50,0)),5)="Store"),$A$2:$A$50),""))

=LOOKUP(2,1/(LEFT(OFFSET(A2,0,0,MATCH(D4,$A$2:$A$20,0)),4)=VLOOKUP(LEFT(D4,4)&"*",{"Prod","Stor";"Stor","Area"},2,0)),$A$2:$A$20)

 

[JV0710]

Thank you !!!! You are the Champ !!!!!

I have been working on the spreadsheet and . . . here is another way of asking the question . . . Perhaps I should have asked it like this in the first place. . . But your formula above works just fine .

---------
In Column A and for example: In cell D7, if the value in A7 = "A", then "", else, if the value in A7<> "A" then need formula to find in column A from A7 upwards the first cell that has a different letter (A4) and return the corresponding value in column B ----> Answer = 24.

Area lookup queryxx.xls
	A	B	C	D
1
2	A	9
3	B	17		9
4	B	24		9
5	c	11		24
6	c	15		24
7	c	12		24
8	A	14
9	B	12		14
10	c	12		12
11	c	16		12
12	B	19		14
13	c	21		19
14	c	10		19
15	A	13
Sheet1