lookup
 

I have two sets of data, named "Rio" and "Fall". Both have the same fields
in them. Name, Address, Phone and Email. There is duplicate names within
the two sets of data. Each data set by itself, does not have duplicates. I
combined the two list of names and created a unique list.

What I would like to do is using the name in the unique list is to lookup in
first in "Rio", if there is any value/text, return it and stop. If there is
not, I would like to lookup in "Fall" for the same info that was missing in
"Rio". If it is not in either one I would like to return nothing.

In otherwords, I know the name will be in one or the other as I created the
unique list from the two. I also know that the data in "Rio" is more up to
date, so I would preferr the information there. If is not there then I'll
take the info from fall otherwise nothing.

I have tried using embedded if statements along with vlookup's and it's not
working as well as returning zero's.

Any help will be greatly appreciated.

lookup
 

If E1 has the name you want to look up, try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,fall,2,0) ,VLOOKUP(E1,Rio,2,0))

You seem to contradict yourself in your request to return blank if the name
doesn't appear in either list as you also state you know the name will appear
in one or the either since the unique list was compiled from both lists. If
it is possible the name might not appear in either list, you could try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(ISNA(VLOOKUP(E1,f all,1,0)),"",VLOOKUP(E1,fall,2,0)),VLOOKUP(E1,Rio, 2,0))


"Jordan" wrote:

I have two sets of data, named "Rio" and "Fall". Both have the same fields
in them. Name, Address, Phone and Email. There is duplicate names within
the two sets of data. Each data set by itself, does not have duplicates. I
combined the two list of names and created a unique list.

What I would like to do is using the name in the unique list is to lookup in
first in "Rio", if there is any value/text, return it and stop. If there is
not, I would like to lookup in "Fall" for the same info that was missing in
"Rio". If it is not in either one I would like to return nothing.

In otherwords, I know the name will be in one or the other as I created the
unique list from the two. I also know that the data in "Rio" is more up to
date, so I would preferr the information there. If is not there then I'll
take the info from fall otherwise nothing.

I have tried using embedded if statements along with vlookup's and it's not
working as well as returning zero's.

Any help will be greatly appreciated.

lookup
 

Thank you for your help. I will try this.

Sorry for the confusion. What I meant to say is, the name will for sure be
there but I dont know if the corresponding information will be. For Example:
C Jordan will be on the list, but I don't know if an address is listed. If
the first set does have an address, I would prefer it over the second set of
data. If neither list has an address for C Jordan then I would want it to
show nothing.

Thanks again for your help.

"JMB" wrote:

If E1 has the name you want to look up, try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,fall,2,0) ,VLOOKUP(E1,Rio,2,0))

You seem to contradict yourself in your request to return blank if the name
doesn't appear in either list as you also state you know the name will appear
in one or the either since the unique list was compiled from both lists. If
it is possible the name might not appear in either list, you could try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(ISNA(VLOOKUP(E1,f all,1,0)),"",VLOOKUP(E1,fall,2,0)),VLOOKUP(E1,Rio, 2,0))


"Jordan" wrote:

I have two sets of data, named "Rio" and "Fall". Both have the same fields
in them. Name, Address, Phone and Email. There is duplicate names within
the two sets of data. Each data set by itself, does not have duplicates. I
combined the two list of names and created a unique list.

What I would like to do is using the name in the unique list is to lookup in
first in "Rio", if there is any value/text, return it and stop. If there is
not, I would like to lookup in "Fall" for the same info that was missing in
"Rio". If it is not in either one I would like to return nothing.

In otherwords, I know the name will be in one or the other as I created the
unique list from the two. I also know that the data in "Rio" is more up to
date, so I would preferr the information there. If is not there then I'll
take the info from fall otherwise nothing.

I have tried using embedded if statements along with vlookup's and it's not
working as well as returning zero's.

Any help will be greatly appreciated.

lookup
 

I think I understand. If the name appears in "Rio" and the address is not
blank, use Rio. Else, return the address from "Fall" (blank if no address).
And you are assured that if the name is not in Rio, it has to be in Fall
since the master list was compiled from these two lists (so no need to verify
it actually exists in "Fall").

Using the assumption E1 contains the name to lookup and the address is the
second field of each list (Rio and Fall), try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,Fall,2,0) ,IF(VLOOKUP(E1,Rio,2,0)="",VLOOKUP(E1,Fall,2,0),VL OOKUP(E1,Rio,2,0)))

and apply a custom number format (Format/Cells/Custom) of:
0;-0;
which will display numbers and text, but will suppress 0's (a zero will be
returned by Vlookup if the address in "Fall" is blank). The custom format
will eliminate the need to test for an empty address in "Fall" and saves a
step. If you can use two helper cells to put the Vlookups in (one for Rio
and one for Fall), you could replace each Vlookup in the formula w/a
reference to the cell that contains the vlookup for each list. So say F2
contains the vlookup for Rio and F3 contains the vlookup for fall. The
formula changes to:

=IF(ISNA(F2),F3,IF(F2=0,F3,F2))


Without using the number format, the formula would look something like:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(VLOOKUP(E1,Fall,2 ,0)="","",VLOOKUP(E1,Fall,2,0)),IF(VLOOKUP(E1,Rio, 2,0)="",IF(VLOOKUP(E1,Fall,2,0)="","",VLOOKUP(E1,F all,2,0)),VLOOKUP(E1,Rio,2,0)))

And, just for the sake of one other avenue that doesn't use IF (it can
sometimes be easy to run into the 7 level nesting limit using lots of IF
statements and using IF would be a mess if you had more than two tables to
deal with):
=CHOOSE(MAX((INDEX(Fall,,1)=E1)*(INDEX(Fall,,2)<" "),(INDEX(Rio,,1)=E1)*(INDEX(Rio,,2)<"")*2)+1,"", VLOOKUP(E1,Fall,2,0),VLOOKUP(E1,Rio,2,0))

array entered w/Cntrl+Shift+Enter.


"Jordan" wrote:

Thank you for your help. I will try this.

Sorry for the confusion. What I meant to say is, the name will for sure be
there but I dont know if the corresponding information will be. For Example:
C Jordan will be on the list, but I don't know if an address is listed. If
the first set does have an address, I would prefer it over the second set of
data. If neither list has an address for C Jordan then I would want it to
show nothing.

Thanks again for your help.

"JMB" wrote:

If E1 has the name you want to look up, try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,fall,2,0) ,VLOOKUP(E1,Rio,2,0))

You seem to contradict yourself in your request to return blank if the name
doesn't appear in either list as you also state you know the name will appear
in one or the either since the unique list was compiled from both lists. If
it is possible the name might not appear in either list, you could try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(ISNA(VLOOKUP(E1,f all,1,0)),"",VLOOKUP(E1,fall,2,0)),VLOOKUP(E1,Rio, 2,0))


"Jordan" wrote:

I have two sets of data, named "Rio" and "Fall". Both have the same fields
in them. Name, Address, Phone and Email. There is duplicate names within
the two sets of data. Each data set by itself, does not have duplicates. I
combined the two list of names and created a unique list.

What I would like to do is using the name in the unique list is to lookup in
first in "Rio", if there is any value/text, return it and stop. If there is
not, I would like to lookup in "Fall" for the same info that was missing in
"Rio". If it is not in either one I would like to return nothing.

In otherwords, I know the name will be in one or the other as I created the
unique list from the two. I also know that the data in "Rio" is more up to
date, so I would preferr the information there. If is not there then I'll
take the info from fall otherwise nothing.

I have tried using embedded if statements along with vlookup's and it's not
working as well as returning zero's.

Any help will be greatly appreciated.

lookup
 

Thank you very much. This helps alot.

"JMB" wrote:

I think I understand. If the name appears in "Rio" and the address is not
blank, use Rio. Else, return the address from "Fall" (blank if no address).
And you are assured that if the name is not in Rio, it has to be in Fall
since the master list was compiled from these two lists (so no need to verify
it actually exists in "Fall").

Using the assumption E1 contains the name to lookup and the address is the
second field of each list (Rio and Fall), try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,Fall,2,0) ,IF(VLOOKUP(E1,Rio,2,0)="",VLOOKUP(E1,Fall,2,0),VL OOKUP(E1,Rio,2,0)))

and apply a custom number format (Format/Cells/Custom) of:
0;-0;
which will display numbers and text, but will suppress 0's (a zero will be
returned by Vlookup if the address in "Fall" is blank). The custom format
will eliminate the need to test for an empty address in "Fall" and saves a
step. If you can use two helper cells to put the Vlookups in (one for Rio
and one for Fall), you could replace each Vlookup in the formula w/a
reference to the cell that contains the vlookup for each list. So say F2
contains the vlookup for Rio and F3 contains the vlookup for fall. The
formula changes to:

=IF(ISNA(F2),F3,IF(F2=0,F3,F2))


Without using the number format, the formula would look something like:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(VLOOKUP(E1,Fall,2 ,0)="","",VLOOKUP(E1,Fall,2,0)),IF(VLOOKUP(E1,Rio, 2,0)="",IF(VLOOKUP(E1,Fall,2,0)="","",VLOOKUP(E1,F all,2,0)),VLOOKUP(E1,Rio,2,0)))

And, just for the sake of one other avenue that doesn't use IF (it can
sometimes be easy to run into the 7 level nesting limit using lots of IF
statements and using IF would be a mess if you had more than two tables to
deal with):
=CHOOSE(MAX((INDEX(Fall,,1)=E1)*(INDEX(Fall,,2)<" "),(INDEX(Rio,,1)=E1)*(INDEX(Rio,,2)<"")*2)+1,"", VLOOKUP(E1,Fall,2,0),VLOOKUP(E1,Rio,2,0))

array entered w/Cntrl+Shift+Enter.


"Jordan" wrote:

Thank you for your help. I will try this.

Sorry for the confusion. What I meant to say is, the name will for sure be
there but I dont know if the corresponding information will be. For Example:
C Jordan will be on the list, but I don't know if an address is listed. If
the first set does have an address, I would prefer it over the second set of
data. If neither list has an address for C Jordan then I would want it to
show nothing.

Thanks again for your help.

"JMB" wrote:

If E1 has the name you want to look up, try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,fall,2,0) ,VLOOKUP(E1,Rio,2,0))

You seem to contradict yourself in your request to return blank if the name
doesn't appear in either list as you also state you know the name will appear
in one or the either since the unique list was compiled from both lists. If
it is possible the name might not appear in either list, you could try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(ISNA(VLOOKUP(E1,f all,1,0)),"",VLOOKUP(E1,fall,2,0)),VLOOKUP(E1,Rio, 2,0))


"Jordan" wrote:

I have two sets of data, named "Rio" and "Fall". Both have the same fields
in them. Name, Address, Phone and Email. There is duplicate names within
the two sets of data. Each data set by itself, does not have duplicates. I
combined the two list of names and created a unique list.

What I would like to do is using the name in the unique list is to lookup in
first in "Rio", if there is any value/text, return it and stop. If there is
not, I would like to lookup in "Fall" for the same info that was missing in
"Rio". If it is not in either one I would like to return nothing.

In otherwords, I know the name will be in one or the other as I created the
unique list from the two. I also know that the data in "Rio" is more up to
date, so I would preferr the information there. If is not there then I'll
take the info from fall otherwise nothing.

I have tried using embedded if statements along with vlookup's and it's not
working as well as returning zero's.

Any help will be greatly appreciated.

lookup
 

quite welcome, thanks for the feedback.

"Jordan" wrote:

Thank you very much. This helps alot.

"JMB" wrote:

I think I understand. If the name appears in "Rio" and the address is not
blank, use Rio. Else, return the address from "Fall" (blank if no address).
And you are assured that if the name is not in Rio, it has to be in Fall
since the master list was compiled from these two lists (so no need to verify
it actually exists in "Fall").

Using the assumption E1 contains the name to lookup and the address is the
second field of each list (Rio and Fall), try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,Fall,2,0) ,IF(VLOOKUP(E1,Rio,2,0)="",VLOOKUP(E1,Fall,2,0),VL OOKUP(E1,Rio,2,0)))

and apply a custom number format (Format/Cells/Custom) of:
0;-0;
which will display numbers and text, but will suppress 0's (a zero will be
returned by Vlookup if the address in "Fall" is blank). The custom format
will eliminate the need to test for an empty address in "Fall" and saves a
step. If you can use two helper cells to put the Vlookups in (one for Rio
and one for Fall), you could replace each Vlookup in the formula w/a
reference to the cell that contains the vlookup for each list. So say F2
contains the vlookup for Rio and F3 contains the vlookup for fall. The
formula changes to:

=IF(ISNA(F2),F3,IF(F2=0,F3,F2))


Without using the number format, the formula would look something like:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(VLOOKUP(E1,Fall,2 ,0)="","",VLOOKUP(E1,Fall,2,0)),IF(VLOOKUP(E1,Rio, 2,0)="",IF(VLOOKUP(E1,Fall,2,0)="","",VLOOKUP(E1,F all,2,0)),VLOOKUP(E1,Rio,2,0)))

And, just for the sake of one other avenue that doesn't use IF (it can
sometimes be easy to run into the 7 level nesting limit using lots of IF
statements and using IF would be a mess if you had more than two tables to
deal with):
=CHOOSE(MAX((INDEX(Fall,,1)=E1)*(INDEX(Fall,,2)<" "),(INDEX(Rio,,1)=E1)*(INDEX(Rio,,2)<"")*2)+1,"", VLOOKUP(E1,Fall,2,0),VLOOKUP(E1,Rio,2,0))

array entered w/Cntrl+Shift+Enter.


"Jordan" wrote:

Thank you for your help. I will try this.

Sorry for the confusion. What I meant to say is, the name will for sure be
there but I dont know if the corresponding information will be. For Example:
C Jordan will be on the list, but I don't know if an address is listed. If
the first set does have an address, I would prefer it over the second set of
data. If neither list has an address for C Jordan then I would want it to
show nothing.

Thanks again for your help.

"JMB" wrote:

If E1 has the name you want to look up, try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),VLOOKUP(E1,fall,2,0) ,VLOOKUP(E1,Rio,2,0))

You seem to contradict yourself in your request to return blank if the name
doesn't appear in either list as you also state you know the name will appear
in one or the either since the unique list was compiled from both lists. If
it is possible the name might not appear in either list, you could try:

=IF(ISNA(VLOOKUP(E1,Rio,1,0)),IF(ISNA(VLOOKUP(E1,f all,1,0)),"",VLOOKUP(E1,fall,2,0)),VLOOKUP(E1,Rio, 2,0))


"Jordan" wrote:

I have two sets of data, named "Rio" and "Fall". Both have the same fields
in them. Name, Address, Phone and Email. There is duplicate names within
the two sets of data. Each data set by itself, does not have duplicates. I
combined the two list of names and created a unique list.

What I would like to do is using the name in the unique list is to lookup in
first in "Rio", if there is any value/text, return it and stop. If there is
not, I would like to lookup in "Fall" for the same info that was missing in
"Rio". If it is not in either one I would like to return nothing.

In otherwords, I know the name will be in one or the other as I created the
unique list from the two. I also know that the data in "Rio" is more up to
date, so I would preferr the information there. If is not there then I'll
take the info from fall otherwise nothing.

I have tried using embedded if statements along with vlookup's and it's not
working as well as returning zero's.

Any help will be greatly appreciated.