Comparing 2 tables to find missing detail
 

I have a list of journal entries that is missing some detail (Table
A). IN Another table (Table B) I have the year's listing of journal
entries that contains this detail plus a lot more. What formula can I
write in table A that will find the associated record in B (say based
on journal number and dollar amount) that will bring across the
correct detail for each line.

They are both arranged in columns in a similar structure and contain
both text and numbers.

I know this but am having a brain fade!

Thanks

Comparing 2 tables to find missing detail
 

Excel does not have lines. It has rows and columns.

Tyro

wrote in message
...
I have a list of journal entries that is missing some detail (Table
A). IN Another table (Table B) I have the year's listing of journal
entries that contains this detail plus a lot more. What formula can I
write in table A that will find the associated record in B (say based
on journal number and dollar amount) that will bring across the
correct detail for each line.

They are both arranged in columns in a similar structure and contain
both text and numbers.

I know this but am having a brain fade!

Thanks

Comparing 2 tables to find missing detail
 

In sheet: Table A,
Assuming the journal # and amounts are listed in A2:B2 down,
you could place this in C2's formula bar, then array-enter the formula by
pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX('Table B'!C$2:C$10,MATCH(1,('Table B'!$A$2:$A$10=$A2)*('Table
B'!$B$2:$B$10=$B2),0))
Copy C2 across/fill down as far as required to extract cols C, D, etc from
sheet: Table B. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
I have a list of journal entries that is missing some detail (Table
A). IN Another table (Table B) I have the year's listing of journal
entries that contains this detail plus a lot more. What formula can I
write in table A that will find the associated record in B (say based
on journal number and dollar amount) that will bring across the
correct detail for each line.

They are both arranged in columns in a similar structure and contain
both text and numbers.

I know this but am having a brain fade!

Thanks

Comparing 2 tables to find missing detail
 

Max,

I am not trying to put down your formula but my reading of the OP:

write in table A that will find the associated record in B (say based
on journal number and dollar amount)

suggests that there is the possibility that there could be two or more
journal # the same. If that is so then, if the dollar amounts are also by
chance the same as well, your formula will bring across the details of the
first match in the second journal # Row

If there will never be two similar journal #'s then you don't need the
second comparison in the MATCH() so simply:

=INDEX('Table B'!C$2:C$10,MATCH($A2,'Table B'$A$2:$A$10,0))

normally entered will do the job - or am I misreading it?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Max" wrote in message
...
In sheet: Table A,
Assuming the journal # and amounts are listed in A2:B2 down,
you could place this in C2's formula bar, then array-enter the formula by
pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX('Table B'!C$2:C$10,MATCH(1,('Table B'!$A$2:$A$10=$A2)*('Table
B'!$B$2:$B$10=$B2),0))
Copy C2 across/fill down as far as required to extract cols C, D, etc from
sheet: Table B. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
I have a list of journal entries that is missing some detail (Table
A). IN Another table (Table B) I have the year's listing of journal
entries that contains this detail plus a lot more. What formula can I
write in table A that will find the associated record in B (say based
on journal number and dollar amount) that will bring across the
correct detail for each line.

They are both arranged in columns in a similar structure and contain
both text and numbers.

I know this but am having a brain fade!

Thanks

Comparing 2 tables to find missing detail
 

OP should try both index/match variations provided
and feedback to us here

Earlier, on the face of this part:
.. (say based on journal number and dollar amount)
it suggests a dual criteria
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Comparing 2 tables to find missing detail
 

Sandy,
Guess the OP is apparently speechless with delight at our responses. It must
have worked, it must have ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Comparing 2 tables to find missing detail
 

I think that he/she/they, (deniseandgavin), would be satisfied with your
formula because, now that I think about it, in my very limited experience of
monarary matters, (God knows that I am not very good with money, that's why
he doesn't give me very much), the only Journal entries that I encountered
at work were all unique numbers.

I assume now that in the *associated record in B (say based on journal
number and dollar amount)* the dollar amount was and example of what the
missing data could be but then you probably knew that already.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Max" wrote in message
...
Sandy,
Guess the OP is apparently speechless with delight at our responses. It
must have worked, it must have ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Comparing 2 tables to find missing detail
 

Sandy, the earlier dig was intended for the OP. In the sad absence of OPs
reversion/participation in discussions (a general obs, btw), who knows?
We care, but OPs themselves?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---