-1

I have a need to combining several excel worksheets into a single Master list. I'm stuck trying to populate a cell in my worksheet with one of 2 values from another worksheet (Staging_2) in the same workbook.

This formula, originating from my master worksheet, needs to look at cell C2 in the Staging_2 worksheet and determine if it's has data in it. If it does, I want to copy everything in that cell to my master worksheet. If Staging_2 cell C2 is blank, I want to copy the value from Staging_2, cell B2 to the Master worksheet.

NOTE: the option to use VB code is not available to me, so it has to be some sort of formula

I can easily map cells, and have even used the mid formula to find specific information in another area, but I cant figure out how to compare 2 cells on a different worksheet and take one value if it's populated, or another if it's not.

user20841964
  • 1

1 Answers1

0

I don't see why an IF statement couldn't get the job done, however without images or examples it is difficult to know what you need to do.

=IF(Staging_2!C2<>"",Staging_2!C2,Staging_2!B2)

UPDATE:

=IF(IFNA(INDEX(Staging_2!A:C,MATCH(A1,Staging_2!A:A,0),3),"")<>"",INDEX(Staging_2!A:C,MATCH(A1,Staging_2!A:A,0),3),INDEX(Staging_2!A:C,MATCH(A1,Staging_2!A:A,0),2))

Explanation: If you break down the formula it's really only 3 of the same part.

INDEX(Staging_2!A:C,MATCH(A1,Staging_2!A:A,0),3)

This is storing Column A through C of the Staging worksheet, then grabbing the row that matches the cell A1 (unique id) and returning column 3 (column c). The IFNA is simply handling the error when there is no value (column c is empty) and setting it to an empty string. So when there is a value, return that value, else return column 2 (column b).

Kevin P.
  • 907
  • 7
  • 18
  • Sorry I missed an important piece of information in my write up. I have a unique identifying in my master list Called ID. A1 in the staging worksheet has a list of unique ID's too. I need to pull from C2 or B2 in that Staging_2 worksheet based on those unique ID's matching. So I was assuming I had to so a vlookup and then do an If, which was way to advanced for my excel skills – user20841964 Dec 22 '22 at 20:09
  • Take a look at the updated formula, this should work and if you need this to work for multiple rows, which it sounds like you may, considering the ID column, than it can be dragged down. – Kevin P. Dec 22 '22 at 20:24
  • This requires the unique ID's in the staging worksheet to be in Column A, the corresponding data to be in Column B or C, and in the Master sheet it looks at cell A1 for an ID and returns the relative set of data from either B or C. – Kevin P. Dec 22 '22 at 20:30