Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"The Good Deeds Team" wrote in
message ...
yes, everything you said worked perfectly - you are the one.

"RagDyer" wrote:

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 ...
I have 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

=(second spreadsheet!$F$11)

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
second spreadsheet, say column g

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.