Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and Lookup
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and Lookup
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and Lookup
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and Lookup
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and Lookup
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and Lookup
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |