Multiple functions--Two spreadsheets

[brady23]

Hello,

I have two spreadsheets (One (older sheet) with full data in it and another (new daily wkst) missing certain names from column A), and I am trying to compare the new spreadsheet with the old to fill in missing cells in Column A.

I am trying to fill in blanks in Cell A in the new worksheet with names from Column A in the old sheet.

I've used Index and Match for somethings, but I don't know how to use these types of functions searching/matching four criteria...

Basically I am taking a unique identifier in column P (from old worksheet) and linking that to the name in Column A from old worksheet. Both sheets have the same unique identifier in column P, which is a possible way to fill in the missing names in the new worksheet.


Any ideas would greatly be appreciated!!

I am stumped.

Chris

[rscsmith]

Hi Chris

Sumproduct may be able to help. Assuming the criteria identify a unique record, you could use this to return the row number you're interested in. This would be the input to an INDEX function. You could additional criteria for other columns.

For example, the following would return the row where column P contains "Smith" and column R contains NSW.

=SUMPRODUCT(($P$1:$P$99="Smith")*($R$1:$R$99="NSW")*ROW($P$1:$P$99))

Notes: You cannot use $P:$P, you have to use row references also. All the ranges should have the same number of rows. Obviously, you could substitute the hard coded strings in the above with cell references. Your final formula would look something like:

=INDEX($A:$A,SUMPRODUCT(($P$1:$P$99="Smith")*($R$1:$R$99="NSW")*ROW($P$1:$P$99)))

I hope this gives you some hints.

Best regards, Rob.

[ctallen23]

Hi,

That didn't work. I attached two new files. both files have two columns of certainty--Columns C and I. I want to fill Columns A and B in GlobalProject2, with data from GlobalProject. Basically I've been using If/index/Match in hopes of reading files and columns, and then inputting a rep's name into column A in GlobalProject2. It hasn't worked.

My idea is this: Take GlobalProject2 and if there is a blank in Column A, then go to column I and read the Sedol. Once the Sedol is verified, go to GlobalProject and read that Sedol in that Column I. Once that Sedol is read in the GlobalProject file, match that Sedol to who's name is in Column A. Ouput that name to column A in GlobalProject2.

I want to work on the VBA later. For the time being, I am trying to use Excel functions so to learn how combining them (i.e. Index, Match, IF, etc...) can get a project, like this, to work.

Thanks,

Chris