How to do iferror vlookup in r

Question

I have created a formula in excel with iferror and vlookup(mentioned below).Can you please let me know how can I replicate the same in R

This lookup goes to main_df which matches with various column named E&F

So the condition is vlookup on column E with sheet1 if error then vlookup on F with sheet2 and if error then again vlookup upon E with sheet3.So basically every time I get a NA it should pick up only those values and do the next vlookup.

IFERROR(IFERROR(VLOOKUP($E2,'sheet1'!$K:$L,2,0),VLOOKUP($F2,'sheet2'!$A:$B,2,0)),VLOOKUP($E2,'sheet3'!$N:$O,2,0))

Edit:

   main_df
    Countries   City
    USA         Texas
    India       Mumbai
    China       Hunan
    Veitnam     Hue

Other DF

df_sheet1                            df_sheet2
    Countries Population          City      Population
    USA       1000000             Hunan     239857688
    India     118947759           Hue       667588

Desired output final_df

main_df
Countries   City      Population
USA         Texas     1000000
India       Mumbai    118947759
China       Hunan     239857688
Veitnam     Hue       667588

I have quoted the excel formula to make it clear..Have already explained what I am excatly looking for.The output I am looking for is all the NA should have matched values as per as the look up done. So letz say have df with country names and the rest 3 sheet1,sheet2,sheet3 have population different sheet has different country name and population number.So the desired output would be vlook up and get all the countries population in main df.Hope that explains — Arkadeep Paul Choudhury, Jun 23 '16 at 08:03
Provide [reproducible example](http://stackoverflow.com/questions/5963269). sheet1, sheet2, sheet3 are 3 different dataframes? — zx8754, Jun 23 '16 at 08:07
Wouldn't `rbind` solve your problem here ? Just get your all data together and then merge it . — Pankaj Kaundal, Jun 23 '16 at 09:26
not possible since I need to do next vlook up when only it get NA with first vlook up — Arkadeep Paul Choudhury, Jun 23 '16 at 09:32
Possible duplicate of [Merging more than 2 dataframes in R by rownames](http://stackoverflow.com/questions/16666643/merging-more-than-2-dataframes-in-r-by-rownames) — Pankaj Kaundal, Jun 23 '16 at 09:36

Pankaj Kaundal · Answer 1 · 2016-06-23T09:34:20.617

i assume you have two dfs as:

> data1
  Countries Population
1       USA    1000000
2     India  118947759

> data2
  Countries Population
1    Turkey     567748
2        UK    9857688
3     China  239857688
4    Canada     667588

> tomerge
  Countries
1       USA
2        UK
3     India
4     China
5   Denmark
6    Canada
7    Turkey

Run this function i found on a question on SO

MyMerge <- function(x, y){
              df <- merge(x, y, by= "Countries", all.x= TRUE, all.y= TRUE)
              return(df)
            }

Then using Reduce

final <- Reduce(MyMerge,list(data1,data2,tomerge))


> final
  Countries Population.x Population.y
1     India    118947759           NA
2       USA      1000000           NA
3    Canada           NA       667588
4     China           NA    239857688
5    Turkey           NA       567748
6        UK           NA      9857688
7   Denmark           NA           NA

The concern is that that the merge is from different df and not one...and hence the mention of df_sheet1,df_sheet2 and df_sheet3 and the main df has only counties — Arkadeep Paul Choudhury, Jun 23 '16 at 09:10
some what solves it but again the concern is need to do lookup for multiple column from the main df(tomerge-df) but the function gives option to merge from only one column in the df 'countries' and assume I have 'region' also in the main df to map. — Arkadeep Paul Choudhury, Jun 23 '16 at 10:37

How to do iferror vlookup in r

1 Answers1