Remember Me?

 The Good Deeds Team Posts: n/a How do I connect fields from two spreadsheets

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second spreadsheet

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I want
it to look through coulmn F and find the value 20, which matches the value in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that need
to match 6000 accounts in the second spreadsheet, and I don't want to code it
row by row, simplely match the accopunt numbers from the two spreadsheets and
give me a value on the corresponding row.

 RagDyer Posts: n/a You can use Vlookup.

Account numbers on Sheet1, A2 to A6000.

Account numbers on Sheet2, A2 to A6000
Amounts on Sheet2, B2 to B6000.

Enter this in B2 of Sheet1:

=VLOOKUP(A2,Sheet2!\$A\$2:\$B\$6000,2,0)

You can drag down to copy, or the easy way is to double click on the "fill
handle" in the lower right corner of B2, which will copy the formula in B2
down Column B, as far as there is data in Column A.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"The Good Deeds Team" wrote in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second spreadsheet

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I want
it to look through coulmn F and find the value 20, which matches the value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that need
to match 6000 accounts in the second spreadsheet, and I don't want to code
it
row by row, simplely match the accopunt numbers from the two spreadsheets
and
give me a value on the corresponding row.

 Otto Moehrbach Posts: n/a You can do this with VLookup formulas but there is a problem with that, that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the file
will grow big in a hurry. If you have more than just those 2 columns, which
I suspect you do, you will end up with a big file. If that is not a problem
for you then I would say to go with the formulas. The alternative is to go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team" wrote in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second spreadsheet

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I want
it to look through coulmn F and find the value 20, which matches the value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that
need
to match 6000 accounts in the second spreadsheet, and I don't want to code
it
row by row, simplely match the accopunt numbers from the two spreadsheets
and
give me a value on the corresponding row.

 RagDyer Posts: n/a You bring up a good point Otto.

The Index and Match combination is supposed to be much more efficient then
Vlookup, although I have no idea how it compares to VBA.

This should work faster then the Vlookup formula I first suggested:

=INDEX(Sheet2!\$B\$2:\$B\$6000,MATCH(A2,Sheet2!\$A\$2:\$A \$6000,0))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
You can do this with VLookup formulas but there is a problem with that, that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the file
will grow big in a hurry. If you have more than just those 2 columns, which
I suspect you do, you will end up with a big file. If that is not a problem
for you then I would say to go with the formulas. The alternative is to go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team" wrote in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second spreadsheet

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I want
it to look through coulmn F and find the value 20, which matches the value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that
need
to match 6000 accounts in the second spreadsheet, and I don't want to code
it
row by row, simplely match the accopunt numbers from the two spreadsheets
and
give me a value on the corresponding row.

 Otto Moehrbach Posts: n/a RD
The VBA way may well be slower (looping through 6000 cells) but it
doesn't increase the size of the file like all those formulas do. I usually
refrain from using formulas when there are many (like 6000) formulas
involved. Otto
"RagDyer" wrote in message
...
You bring up a good point Otto.

The Index and Match combination is supposed to be much more efficient then
Vlookup, although I have no idea how it compares to VBA.

This should work faster then the Vlookup formula I first suggested:

=INDEX(Sheet2!\$B\$2:\$B\$6000,MATCH(A2,Sheet2!\$A\$2:\$A \$6000,0))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
You can do this with VLookup formulas but there is a problem with that,
that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the file
will grow big in a hurry. If you have more than just those 2 columns,
which
I suspect you do, you will end up with a big file. If that is not a
problem
for you then I would say to go with the formulas. The alternative is to
go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team" wrote
in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull
the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second spreadsheet

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I
want
it to look through coulmn F and find the value 20, which matches the
value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that
need
to match 6000 accounts in the second spreadsheet, and I don't want to
code
it
row by row, simplely match the accopunt numbers from the two spreadsheets
and
give me a value on the corresponding row.

 RagDyer Posts: n/a FWIW,
switched a big WB database from a double (error checking) Vlookup formula to
the Index and Match combination,
It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes !

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
RD
The VBA way may well be slower (looping through 6000 cells) but it
doesn't increase the size of the file like all those formulas do. I

usually
refrain from using formulas when there are many (like 6000) formulas
involved. Otto
"RagDyer" wrote in message
...
You bring up a good point Otto.

The Index and Match combination is supposed to be much more efficient

then
Vlookup, although I have no idea how it compares to VBA.

This should work faster then the Vlookup formula I first suggested:

=INDEX(Sheet2!\$B\$2:\$B\$6000,MATCH(A2,Sheet2!\$A\$2:\$A \$6000,0))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
You can do this with VLookup formulas but there is a problem with that,
that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the

file
will grow big in a hurry. If you have more than just those 2 columns,
which
I suspect you do, you will end up with a big file. If that is not a
problem
for you then I would say to go with the formulas. The alternative is to
go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team" wrote
in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull
the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I
want
it to look through coulmn F and find the value 20, which matches the
value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that
need
to match 6000 accounts in the second spreadsheet, and I don't want to
code
it
row by row, simplely match the accopunt numbers from the two

and
give me a value on the corresponding row.

 Otto Moehrbach Posts: n/a That is significant. I will remember that. Thanks. Otto
"RagDyer" wrote in message
...
FWIW,
switched a big WB database from a double (error checking) Vlookup formula
to
the Index and Match combination,
It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes !

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
RD
The VBA way may well be slower (looping through 6000 cells) but it
doesn't increase the size of the file like all those formulas do. I

usually
refrain from using formulas when there are many (like 6000) formulas
involved. Otto
"RagDyer" wrote in message
...
You bring up a good point Otto.

The Index and Match combination is supposed to be much more efficient

then
Vlookup, although I have no idea how it compares to VBA.

This should work faster then the Vlookup formula I first suggested:

=INDEX(Sheet2!\$B\$2:\$B\$6000,MATCH(A2,Sheet2!\$A\$2:\$A \$6000,0))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
You can do this with VLookup formulas but there is a problem with that,
that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the

file
will grow big in a hurry. If you have more than just those 2 columns,
which
I suspect you do, you will end up with a big file. If that is not a
problem
for you then I would say to go with the formulas. The alternative is
to
go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team"
wrote
in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the
second
spreadsheet for the corresponding account number, example 20, and pull
the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column
F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I
want
it to look through coulmn F and find the value 20, which matches the
value
in
the first spreadsheet, and then give me the amount from that row in
the

this application is really 6000 accounts in the first spreadsheet,
that
need
to match 6000 accounts in the second spreadsheet, and I don't want to
code
it
row by row, simplely match the accopunt numbers from the two

and
give me a value on the corresponding row.

 The Good Deeds Team Posts: n/a This is really cool and worked great - now I would like to extend the model.

The application is a budget model

Budgets are stored in 140 separate worksheets, I thought about, having 140
tabs, but someone in the office said that would not work for them

Your suggestion worked for me as long as the other spreahseet was open (as
in the following example)

=-ROUND(INDEX('[10010 Budget Ofc of the President.xls]Sheet 1'!\$F\$1:\$F\$100,
MATCH(\$A503,'[10010 Budget Ofc of the President.xls]Sheet 1'!\$M\$1:'[10010
Budget Ofc of the President.xls]Sheet 1'!\$M\$100,0))/3,2)

However if I close the other spreadsheet, and the first spreadsheet as well,
then open the first spreadsheet and say update from other spreadsheets, the
fields gets the value REF# and the formula changes to this:

=-ROUND(INDEX('S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget
Ofc of the President.xls]Sheet 1'!\$F\$1:\$F\$100,
MATCH(\$A503,'S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget
Ofc of the President.xls]Sheet 1'!\$M\$1:'S:\ACCOUNTING\Budgets\Budgets\FY2005
Budget\OOP\[10010 Budget Ofc of the President.xls]Sheet 1'!\$M\$100,0))/3,2)

Even if this worked, I wanted to reduce the string

S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\

to

..\oop

that does not seem to work either

Do you have any suggestions?

"RagDyer" wrote:

FWIW,
switched a big WB database from a double (error checking) Vlookup formula to
the Index and Match combination,
It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes !

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
RD
The VBA way may well be slower (looping through 6000 cells) but it
doesn't increase the size of the file like all those formulas do. I

usually
refrain from using formulas when there are many (like 6000) formulas
involved. Otto
"RagDyer" wrote in message
...
You bring up a good point Otto.

The Index and Match combination is supposed to be much more efficient

then
Vlookup, although I have no idea how it compares to VBA.

This should work faster then the Vlookup formula I first suggested:

=INDEX(Sheet2!\$B\$2:\$B\$6000,MATCH(A2,Sheet2!\$A\$2:\$A \$6000,0))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
You can do this with VLookup formulas but there is a problem with that,
that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the

file
will grow big in a hurry. If you have more than just those 2 columns,
which
I suspect you do, you will end up with a big file. If that is not a
problem
for you then I would say to go with the formulas. The alternative is to
go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team" wrote
in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull
the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I
want
it to look through coulmn F and find the value 20, which matches the
value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that
need
to match 6000 accounts in the second spreadsheet, and I don't want to
code
it
row by row, simplely match the accopunt numbers from the two

and
give me a value on the corresponding row.

 The Good Deeds Team Posts: n/a if the first table has the account number, but it does not exist in the
second table, the result is '#N/A', how can I make the result 0 (zero)

"RagDyer" wrote:

You bring up a good point Otto.

The Index and Match combination is supposed to be much more efficient then
Vlookup, although I have no idea how it compares to VBA.

This should work faster then the Vlookup formula I first suggested:

=INDEX(Sheet2!\$B\$2:\$B\$6000,MATCH(A2,Sheet2!\$A\$2:\$A \$6000,0))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
You can do this with VLookup formulas but there is a problem with that, that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the file
will grow big in a hurry. If you have more than just those 2 columns, which
I suspect you do, you will end up with a big file. If that is not a problem
for you then I would say to go with the formulas. The alternative is to go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team" wrote in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second spreadsheet

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I want
it to look through coulmn F and find the value 20, which matches the value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that
need
to match 6000 accounts in the second spreadsheet, and I don't want to code
it
row by row, simplely match the accopunt numbers from the two spreadsheets
and
give me a value on the corresponding row.

 RagDyer Posts: n/a Try this:

=IF(ISNA(MATCH(A2,Sheet2!\$A\$2:\$A\$6000,0)),0,INDEX( Sheet2!\$B\$2:\$B\$6000,MATCH(
A2,Sheet2!\$A\$2:\$A\$6000,0)))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"The Good Deeds Team" wrote in
message ...
if the first table has the account number, but it does not exist in the
second table, the result is '#N/A', how can I make the result 0 (zero)

"RagDyer" wrote:

You bring up a good point Otto.

The Index and Match combination is supposed to be much more efficient then
Vlookup, although I have no idea how it compares to VBA.

This should work faster then the Vlookup formula I first suggested:

=INDEX(Sheet2!\$B\$2:\$B\$6000,MATCH(A2,Sheet2!\$A\$2:\$A \$6000,0))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Otto Moehrbach" wrote in message
...
You can do this with VLookup formulas but there is a problem with that,

that
you should be aware of. With 6000 rows of data and just 2 columns in
Sheet2, you will need 6000 VLookup formulas. The problem is that the file
will grow big in a hurry. If you have more than just those 2 columns,

which
I suspect you do, you will end up with a big file. If that is not a

problem
for you then I would say to go with the formulas. The alternative is to

go
with a VBA solution. Post back if you need more. HTH Otto
"The Good Deeds Team" wrote

in
message ...

the first has a list of account numbers, for example

10
20
30

The second has a list of acount numbers and an amount, for example

10 100
20 150
30 175

How can I in the fisrt spreadsheet, use a formula to look in the second
spreadsheet for the corresponding account number, example 20, and pull

the
correct value, example 150 withoutout coding each row in the first
spreadsheet to look exactly at the specific row in the second

For example, I can do this and it works

however I just want it to know where in the second spreadsheet column F
the
value 20 exists and pull 175 from colum G

I don't want to have to tell the firts spreadsheet it is on row 11, I

want
it to look through coulmn F and find the value 20, which matches the

value
in
the first spreadsheet, and then give me the amount from that row in the

this application is really 6000 accounts in the first spreadsheet, that
need
to match 6000 accounts in the second spreadsheet, and I don't want to

code
it
row by row, simplely match the accopunt numbers from the two

and
give me a value on the corresponding row.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Linda L Excel Discussion (Misc queries) 1 January 22nd 05 12:58 AM Steven Geordie Boy Excel Discussion (Misc queries) 0 December 22nd 04 03:55 PM Michael MacLachlan Excel Discussion (Misc queries) 0 December 3rd 04 11:15 AM flavi Excel Worksheet Functions 1 December 1st 04 10:57 AM Luke MacNeil Excel Discussion (Misc queries) 3 November 30th 04 03:21 PM

All times are GMT +1. The time now is 04:53 PM. Copyright ©2004-2021 ExcelBanter.