Comparing 2 tables to find missing detail

  • Thread starter Thread starter deniseandgavin
  • Start date Start date
D

deniseandgavin

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
 
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,

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

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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)
Click to expand...
it suggests a dual criteria

---
 
Sandy,
Guess the OP is apparently speechless with delight at our responses. It must
have worked, it must have ...

---
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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?

---
 
Back
Top