ExcelBanter - IF and Lookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - IF and Lookup (https://www.excelbanter.com/excel-worksheet-functions/215672-if-lookup.html)

Hello,

I'm not sure if this can be Done but hopefully you'll be able to help. I've
been trying to do this for weeks now.

Bascially I have a number of stores in different areas.

For example

COL A COL B
AREA1 StoreName1
AREA1 StoreName2
AREA1 StoreName3
AREA1 StoreName4
AREA1 StoreName5
AREA2 StoreName1
AREA2 StoreName2
AREA2 StoreName3
AREA2 StoreName4
AREA2 StoreName5

However i want to display the data on another sheet by selecting which area
number.

I used the following
=LOOKUP(C3,F7:F100,G7:G100)

This only shows the last store name from the selected Area Number, But i
want it the list all stores with that area number, (C3) been where the area
number is enter.

Can you understand what i'm trying to ask.

Any help would be great.

Thanks

Martin

Just a quick Note.

F7:F100 would be Col A in the example and G7:G100 would be col B.#

Thanks

Martin

"Martinc126" wrote:

Hello,

I'm not sure if this can be Done but hopefully you'll be able to help. I've
been trying to do this for weeks now.

Bascially I have a number of stores in different areas.

For example

COL A COL B
AREA1 StoreName1
AREA1 StoreName2
AREA1 StoreName3
AREA1 StoreName4
AREA1 StoreName5
AREA2 StoreName1
AREA2 StoreName2
AREA2 StoreName3
AREA2 StoreName4
AREA2 StoreName5

However i want to display the data on another sheet by selecting which area
number.

I used the following
=LOOKUP(C3,F7:F100,G7:G100)

This only shows the last store name from the selected Area Number, But i
want it the list all stores with that area number, (C3) been where the area
number is enter.

Can you understand what i'm trying to ask.

Any help would be great.

Thanks

Martin

Can you tell me which cell the formula will be going into? Do you have
several cells below this where the storenames will appear?

Pete

On Jan 7, 4:05*pm, Martinc126
wrote:
Hello,

I'm not sure if this can be Done but hopefully you'll be able to help. I've
been trying to do this for weeks now.

Bascially I have a number of stores in different areas.

For example

COL A * * COL B
AREA1 * *StoreName1
AREA1 * *StoreName2
AREA1 * *StoreName3
AREA1 * *StoreName4
AREA1 * *StoreName5
AREA2 * *StoreName1
AREA2 * *StoreName2
AREA2 * *StoreName3
AREA2 * *StoreName4
AREA2 * *StoreName5

However i want to display the data on another sheet by selecting which area
number.

I used the following
=LOOKUP(C3,F7:F100,G7:G100)

This only shows the last store name from the selected Area Number, But i
want it the list all stores with that area number, (C3) been where the area
number is enter.

Can you understand what i'm trying to ask.

Any help would be great.

Thanks

Martin

Hello,

In the example i have 5 Stores in each area so i would like them to appear
in in B5 to B10

Thanks

Martin

"Pete_UK" wrote:

Can you tell me which cell the formula will be going into? Do you have
several cells below this where the storenames will appear?

Pete

On Jan 7, 4:05 pm, Martinc126
wrote:
Hello,

I'm not sure if this can be Done but hopefully you'll be able to help. I've
been trying to do this for weeks now.

Bascially I have a number of stores in different areas.

For example

COL A COL B
AREA1 StoreName1
AREA1 StoreName2
AREA1 StoreName3
AREA1 StoreName4
AREA1 StoreName5
AREA2 StoreName1
AREA2 StoreName2
AREA2 StoreName3
AREA2 StoreName4
AREA2 StoreName5

However i want to display the data on another sheet by selecting which area
number.

I used the following
=LOOKUP(C3,F7:F100,G7:G100)

This only shows the last store name from the selected Area Number, But i
want it the list all stores with that area number, (C3) been where the area
number is enter.

Can you understand what i'm trying to ask.

Any help would be great.

Thanks

Martin

Okay, you need to use a helper column to set up a unique reference for
each area, so I've assumed you can use column H - put this formula in
H7:

=IF(F7="","",F7&"_"&COUNTIF(F$7:F7,F7))

then copy this down to H100 (or beyond).

Then, assuming that C3 is where you would put the area number, put
this formula in B5:

=IF(ISNA(MATCH($C$3&"_"&ROW(A1),H:H,0)),"",INDEX(G :G,MATCH($C$3&"_"&ROW
(A1),H:H,0)))

and then copy this down to B10. Obviously, if you can't use column H,
then substitute all references to H to whichever column you can use.

Hope this helps.

Pete

On Jan 7, 6:12*pm, Martinc126
wrote:
Hello,

In the example i have 5 Stores in each area so i would like them to appear
in in B5 to B10

Thanks

Martin

"Pete_UK" wrote:
Can you tell me which cell the formula will be going into? Do you have
several cells below this where the storenames will appear?

Pete

On Jan 7, 4:05 pm, Martinc126
wrote:
Hello,

I'm not sure if this can be Done but hopefully you'll be able to help.. I've
been trying to do this for weeks now.

Bascially I have a number of stores in different areas.

For example

COL A * * COL B
AREA1 * *StoreName1
AREA1 * *StoreName2
AREA1 * *StoreName3
AREA1 * *StoreName4
AREA1 * *StoreName5
AREA2 * *StoreName1
AREA2 * *StoreName2
AREA2 * *StoreName3
AREA2 * *StoreName4
AREA2 * *StoreName5

However i want to display the data on another sheet by selecting which area
number.

I used the following
=LOOKUP(C3,F7:F100,G7:G100)

This only shows the last store name from the selected Area Number, But i
want it the list all stores with that area number, (C3) been where the area
number is enter.

Can you understand what i'm trying to ask.

Any help would be great.

Thanks

Martin- Hide quoted text -

- Show quoted text -

If your data is sorted or grouped together by area as is shown in your
sample data...

Entered in B5 and copied down to B10:

=INDEX(G$7:G$100,MATCH(C$3,F$7:F$100,0)+ROWS(B$5:B 5)-1)

Where C3 = lookup area

--
Biff
Microsoft Excel MVP

"Martinc126" wrote in message
...
Hello,

In the example i have 5 Stores in each area so i would like them to appear
in in B5 to B10

Thanks

Martin

"Pete_UK" wrote:

Can you tell me which cell the formula will be going into? Do you have
several cells below this where the storenames will appear?

Pete

On Jan 7, 4:05 pm, Martinc126
wrote:
Hello,

I'm not sure if this can be Done but hopefully you'll be able to help.
I've
been trying to do this for weeks now.

Bascially I have a number of stores in different areas.

For example

COL A COL B
AREA1 StoreName1
AREA1 StoreName2
AREA1 StoreName3
AREA1 StoreName4
AREA1 StoreName5
AREA2 StoreName1
AREA2 StoreName2
AREA2 StoreName3
AREA2 StoreName4
AREA2 StoreName5

However i want to display the data on another sheet by selecting which
area
number.

I used the following
=LOOKUP(C3,F7:F100,G7:G100)

This only shows the last store name from the selected Area Number, But
i
want it the list all stores with that area number, (C3) been where the
area
number is enter.

Can you understand what i'm trying to ask.

Any help would be great.

Thanks

Martin