In Worksheet 1 Column A has a list of 1000 different names. For example,
cell A750 = "Ralph". Column B has a corresponding number to the values in
column A. For example, cell B750 = "55". Column Y has a list of names which
change dynamically. Column Z wants to return the corresponding numerical
value found in Column B. For example, if cell Y2 = "Ralph" I want cell Z2 to
have the value "55". What short formula (without 1000 nested statements) can
I write to do this in column Z?

In Worksheet 2 Column AA has a list of 5000 names. Column AB lists a
correponding location for each person listed in Column AA. For example,
Column AA100 = "Don" and cell AB100 = "Chicago". Their are other "Don"s
listed in Column AA but they all have unique corresponding cities listed in
Column AB (as do all the names that have multiple values in Column AA).
Column AC has a numerical value corresponding to the values in Columns AA &
AB. For example, cell AC100 = "99". Column AX dynamically lists names which
are listed in Column AA while Column AY lists their location which is listed
in Column AB. Column AZ wants to return the corresponding numerical value
found in Column AC. For example, if cell AX5 = "Don" & cell AY5 = "Chicago"
I want cell AZ5 to return the value "99". What short formula can I write to
do this in column AZ?

Ken

One way:

Z2: =VLOOKUP(Y2,A:B,2,FALSE)

In article ,
Ken wrote:

In Worksheet 1 Column A has a list of 1000 different names. For example,
cell A750 = "Ralph". Column B has a corresponding number to the values in
column A. For example, cell B750 = "55". Column Y has a list of names which
change dynamically. Column Z wants to return the corresponding numerical
value found in Column B. For example, if cell Y2 = "Ralph" I want cell Z2 to
have the value "55". What short formula (without 1000 nested statements) can
I write to do this in column Z?

One way:

AZ5: =SUMPRODUCT(--(AA1:AA5000=AX5),--(AB1:AB5000=AY5),AC1:AC5000)


In article ,
Ken wrote:

In Worksheet 2 Column AA has a list of 5000 names. Column AB lists a
correponding location for each person listed in Column AA. For example,
Column AA100 = "Don" and cell AB100 = "Chicago". Their are other "Don"s
listed in Column AA but they all have unique corresponding cities listed in
Column AB (as do all the names that have multiple values in Column AA).
Column AC has a numerical value corresponding to the values in Columns AA &
AB. For example, cell AC100 = "99". Column AX dynamically lists names which
are listed in Column AA while Column AY lists their location which is listed
in Column AB. Column AZ wants to return the corresponding numerical value
found in Column AC. For example, if cell AX5 = "Don" & cell AY5 = "Chicago"
I want cell AZ5 to return the value "99". What short formula can I write to
do this in column AZ?