Duplicate entries
 

I need some help, and I am fairly new at this. I have a spreadsheet we fill
out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled
in), 3rd is First name (filled in), forth is Dollars (filled in). I have a
master document with the ID#, Last name, First name, all filled in with data.
I want to compare names in the first document with the master document and
take the ID# from the master and have that entered into the blank ID# column
in the first document. I hope this makes sense...

Duplicate entries
 

Assume the master data is in Sheet1, cols A to C, viz:
ID#, Last name, First name

In the other sheet where col A (ID#) is blank, assuming Last name, First
name data is running in A2:B2 down

In A2, normal ENTER:
=INDEX(Sheet1!A$2:A$100,MATCH(1,INDEX((Sheet1!B$2: B$100=B2)*(Sheet1!C$2:C$100=C2),),0))

Adapt the Sheet1 ranges to suit the extent of your actuals
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Western_man" wrote:
I need some help, and I am fairly new at this. I have a spreadsheet we fill
out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled
in), 3rd is First name (filled in), forth is Dollars (filled in). I have a
master document with the ID#, Last name, First name, all filled in with data.
I want to compare names in the first document with the master document and
take the ID# from the master and have that entered into the blank ID# column
in the first document. I hope this makes sense...

Duplicate entries
 

Typo:
.. assuming Last name, First name data is running in A2:B2 down
"in A2:B2 down" should read: in B2:C2 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

Duplicate entries
 

Wow... that works great, thank you very much
I can follow the formula understanding now, but not sure why you use the *
(multiply) on the last statement... anywho you've saved me weeks of time

"Max" wrote:

Assume the master data is in Sheet1, cols A to C, viz:
ID#, Last name, First name

In the other sheet where col A (ID#) is blank, assuming Last name, First
name data is running in A2:B2 down

In A2, normal ENTER:
=INDEX(Sheet1!A$2:A$100,MATCH(1,INDEX((Sheet1!B$2: B$100=B2)*(Sheet1!C$2:C$100=C2),),0))

Adapt the Sheet1 ranges to suit the extent of your actuals
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Western_man" wrote:
I need some help, and I am fairly new at this. I have a spreadsheet we fill
out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled
in), 3rd is First name (filled in), forth is Dollars (filled in). I have a
master document with the ID#, Last name, First name, all filled in with data.
I want to compare names in the first document with the master document and
take the ID# from the master and have that entered into the blank ID# column
in the first document. I hope this makes sense...

Duplicate entries
 

Wow... thank you so very much, You have saved me so much time. This formula
works perfect, the only question I have would be why the * (multiply) for the
last statement?
Thanks again

"Max" wrote:

Assume the master data is in Sheet1, cols A to C, viz:
ID#, Last name, First name

In the other sheet where col A (ID#) is blank, assuming Last name, First
name data is running in A2:B2 down

In A2, normal ENTER:
=INDEX(Sheet1!A$2:A$100,MATCH(1,INDEX((Sheet1!B$2: B$100=B2)*(Sheet1!C$2:C$100=C2),),0))

Adapt the Sheet1 ranges to suit the extent of your actuals
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Western_man" wrote:
I need some help, and I am fairly new at this. I have a spreadsheet we fill
out with 4 columns, the first column is blank (ID#), 2nd is Last name (filled
in), 3rd is First name (filled in), forth is Dollars (filled in). I have a
master document with the ID#, Last name, First name, all filled in with data.
I want to compare names in the first document with the master document and
take the ID# from the master and have that entered into the blank ID# column
in the first document. I hope this makes sense...

Duplicate entries
 

why the * (multiply) for the last statement?

The multiplication of the 2 conditions (identically sized ranges):
(Sheet1!B$2:B$100=B2)*(Sheet1!C$2:C$100=C2)
will produce a resultant array of ones/zeros depending on where the dual
conditions are simultaneously satisfied or not, something like this:
{0;0;0;1;0;0 ...0}

MATCH(1, {0;0;0;1;0;0 ...0},0)
then returns the relative position of the "1" in the resultant array, ie: 4
for the: INDEX(Sheet1!A$2:A$100
to return the 4th element within Sheet1!A$2:A$100
ie what's in A5
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Western_man" wrote in message
...
Wow... thank you so very much, You have saved me so much time. This
formula
works perfect, the only question I have would be why the * (multiply) for
the
last statement?
Thanks again