Using Nested If & right function across two work sheets

[John Louis]

Hi I'm new to this site & hope I'm posting correctly. I am using this formula it's working fine as long as the data I'm working with is on the same sheet =if(right(d2,1)="r",d2,d2) Now I need to look at a new sheet in my workbook to do the same thing, this new sheet has 1000s rows & columns. How do I use my formula & make sure it's looking at the correct row & column.

Thanks very much for any input.

 

[Scott Huish]

Will there only be potentially 1 of 2 different values in the column? 1 that ends in R and one that doesn't or are you looking to find a value that contains 123?

 

[John Louis]

Yes most of the time only two but there could be up to three in some cases in the column & I'm looking to return the one ending with R if it exist if it doesn't exist return the value that doesn't end with R. Thank you.

 

[Scott Huish]

=IFERROR(VLOOKUP("*R",'published edp report'!CG:CG,1,0),VLOOKUP("*",'published edp report'!CG:CG,1,0))