Find matching data in another workbook or sheet

[Mark R Penn]

I've spent all day editing a large sheets (21000 rows, each being a customer
record), and have found that I've lost the data in one column.

I still have the original, unedited workbook, so do have the missing data
available, but as the editing included deletion of many rows, the row
numbers no longer match, so I can't just copy and paste the column.

What I want to do therefore is:

take the content of two cells from a row in workbook "A", and use that to
find the corresponding row in workbook "B". Then, having found the correct
row, take the contents of one cell in that found row, and insert it into a
given cell back in the original row in workbook "A".

Then repeat for the remaining 20999 rows in workbook "A"!

Is that possible?

Thanks,

Mark

 

[Dave Peterson]

If you had a unique single key column, you could use =vlookup() or
=index(match()).

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

====
But if you have to match on two columns to get the unique match, ...

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

 

[Mark R Penn]

Thanks Dave.

As your reply arrived, I was pulling my hair out trying to work out why I
couldn't get a VLOOKUP formula to work for this - I've never used VLOOKUP
before, and although I didn't use your method exactly in the end, it and the
articles you referred me to helped no end.

What I did in the end was to create one new column on each sheet (the
original sheet and the one with the lookup table), which used CONCATENATE to
combine my 2 pieces of info into one that would be unique to the row. This
is necessary because I may have two rows with "Acme Holdings" in the company
name, and three with "Bill Smith" in the contact name, but using CONCATENATE
to end up with "Acme Holdings Bill Smith" results in something unique to one
row.

I was then trying to use VLOOKUP to compare the values in the two
concatenated columns, and return the value in a third column when a match
was found.

Should work, right?

Well, yes, if you know that "the first column (of the lookup table) should
contain the unique key values on which you will base the lookup"!!! The
VLOOKUP instructions I'd found before you replied didn't mention that, and I
was trying to find data in the 17th column of my lookup table!!

Thanks so much. Just shows how great these groups are, and how one tiny
piece of info can make all the difference!!

Mark

 

[Mark R Penn]

BTW, if I return the VLOOKUP result to a spare column, then copy that column
and "paste special>values" into the actual column I want to fill, I can
delete the lookup table, and am back to exactly where I wanted to be!

Mark

Thanks Dave.

As your reply arrived, I was pulling my hair out trying to work out why I
couldn't get a VLOOKUP formula to work for this - I've never used VLOOKUP
before, and although I didn't use your method exactly in the end, it and
the articles you referred me to helped no end.

What I did in the end was to create one new column on each sheet (the
original sheet and the one with the lookup table), which used CONCATENATE
to combine my 2 pieces of info into one that would be unique to the row.
This is necessary because I may have two rows with "Acme Holdings" in the
company name, and three with "Bill Smith" in the contact name, but using
CONCATENATE to end up with "Acme Holdings Bill Smith" results in something
unique to one row.

I was then trying to use VLOOKUP to compare the values in the two
concatenated columns, and return the value in a third column when a match
was found.

Should work, right?

Well, yes, if you know that "the first column (of the lookup table) should
contain the unique key values on which you will base the lookup"!!! The
VLOOKUP instructions I'd found before you replied didn't mention that, and
I was trying to find data in the 17th column of my lookup table!!

Thanks so much. Just shows how great these groups are, and how one tiny
piece of info can make all the difference!!

Mark

 

[Dave Peterson]

And if that cell to be returned is empty, you'll see a 0.

If you don't expect 0's, you can convert to values and then just edit|replace (0
with leave blank, match entire cell).

But if you do expect numbers, you could change your formula:

You might end up with something like:

=if(iserror(vlookup(...)),"missing from table",if(vlookup(...)="","",
vlookup(...))))

(all one cell.)

BTW, if I return the VLOOKUP result to a spare column, then copy that column
and "paste special>values" into the actual column I want to fill, I can
delete the lookup table, and am back to exactly where I wanted to be!

Mark