Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
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) | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |