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.

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.

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.