Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP? nested, complex, vlookup? The impossible!
I'm trying to reference organzided data from one tab to another tab. The tab
w/ the data has column A-D, Column A is a list alphabetized, Column B is alphabetized names, Column C is a list of different names, & Column D is numbers. Here is an example: Tab X A - AB - AAB - Formula to reference Tab Y Tab Y Column A - Column B - Column C - Column D A - AA - AAA - 1,000 A - AB - AAB - 500 A - AC - AAA - 750 B - AA - AAA - 900 I've done complex, nested formulas, but this seems to be impossible. I need a formula that will reference Column D but w/ Column A-C as teh criteria. Column A has duplicates within itself, Column B has duplicates within itself, & Column C only has 2 options but again duplicates. I can't seem to figure a forumula b/c you can't nest Vlookups unless its within if statement. I can put the forumula together to find the first line in Column A, but can't find anything to make the formula look for "A" in Column A & when it finds it Look for "AB" in Column B, & once it finds that look for "AAB" & once it finds that reference Column D. I have screen shots for more specifics. PLEASE help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP? nested, complex, vlookup? The impossible!
If it were me, and I'm sure there's a fancy formula, but I'm not aware of
it, I would--on my "organized" sheet, create a column that concatenates A, B, and C: =A2&B2&C2, and then you could have your vlookup use THAT as the first column, and be something like =VLOOKUP(A2&B2&C2,mylookuptable,4,false) ************ Hope it helps! Anne Troy www.OfficeArticles.com "ricdik" wrote in message ... I'm trying to reference organzided data from one tab to another tab. The tab w/ the data has column A-D, Column A is a list alphabetized, Column B is alphabetized names, Column C is a list of different names, & Column D is numbers. Here is an example: Tab X A - AB - AAB - Formula to reference Tab Y Tab Y Column A - Column B - Column C - Column D A - AA - AAA - 1,000 A - AB - AAB - 500 A - AC - AAA - 750 B - AA - AAA - 900 I've done complex, nested formulas, but this seems to be impossible. I need a formula that will reference Column D but w/ Column A-C as teh criteria. Column A has duplicates within itself, Column B has duplicates within itself, & Column C only has 2 options but again duplicates. I can't seem to figure a forumula b/c you can't nest Vlookups unless its within if statement. I can put the forumula together to find the first line in Column A, but can't find anything to make the formula look for "A" in Column A & when it finds it Look for "AB" in Column B, & once it finds that look for "AAB" & once it finds that reference Column D. I have screen shots for more specifics. PLEASE help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP? nested, complex, vlookup? The impossible!
Hi!
If the 3 different criteria combined are unique as a group, meaning, there are no dupes of A - AB - AAB: SheetX: A1 = A B1 = AB C1 = AAB =SUMPRODUCT(--(Y!A1:A100=A1),--(Y!B1:B100=B1),--(Y!C1:C100=C1),Y!D1:D100) Biff "ricdik" wrote in message ... I'm trying to reference organzided data from one tab to another tab. The tab w/ the data has column A-D, Column A is a list alphabetized, Column B is alphabetized names, Column C is a list of different names, & Column D is numbers. Here is an example: Tab X A - AB - AAB - Formula to reference Tab Y Tab Y Column A - Column B - Column C - Column D A - AA - AAA - 1,000 A - AB - AAB - 500 A - AC - AAA - 750 B - AA - AAA - 900 I've done complex, nested formulas, but this seems to be impossible. I need a formula that will reference Column D but w/ Column A-C as teh criteria. Column A has duplicates within itself, Column B has duplicates within itself, & Column C only has 2 options but again duplicates. I can't seem to figure a forumula b/c you can't nest Vlookups unless its within if statement. I can put the forumula together to find the first line in Column A, but can't find anything to make the formula look for "A" in Column A & when it finds it Look for "AB" in Column B, & once it finds that look for "AAB" & once it finds that reference Column D. I have screen shots for more specifics. PLEASE help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Vlookup Table | Excel Discussion (Misc queries) | |||
Can Someone Help me With a Nested VLOOKUP | Excel Discussion (Misc queries) | |||
Nested IF Function and VLookup Alternatives | Excel Worksheet Functions | |||
Nested Vlookup or alternative? | Excel Worksheet Functions | |||
Nested vlookup? | Excel Worksheet Functions |