lookup a value from multiple columns of two workbooks
 

I have two files in excel that I need to use some macros or functions to
match the items. That is one of the file is file "A" contains 3 columns named
as Amout, SAP Number(which is blank) and Bill No.

The other file "B" contains 3 columns named as Reference no, Doc No. and
Amount.

I want to get Doc No from "B" file to Column SAP Number of File "A" where
the Bill No. and Amount column values of file "A" and Reference no and Amount
columns value of file "B" matches.

If Bill No. and "Reference no" matches but Amount does not match they can
appear as below or seperate worksheet so that I can investigate these.

lookup a value from multiple columns of two workbooks
 

This can be done with VLOOKUP() but a little more information is needed.

In file 'B', can the Reference no (to be matched with Bill No. in A) show up
more than once in your list but with different values for the Amount? Or
will a Reference number show up only once in file 'B'?

If the Reference No. will only show up once, then these formulas should work
for you. First requirement is that the information in workbook 'B' has to be
sorted by the Reference No, with smallest at the top of the list.

In 'B' your 3 columns go from A2 to C9 (change in formula to match your
range in that book), and are laid out in the sequence you gave: Ref #, Doc #,
Amount.

In book 'A' your entries are in the same range (A2 to C9).

You should have both workbooks opened up when you initially set up these
formulas, but after you set them up, you can close 'B' and it will still work.

In the cell for the first SAP entry (B2) put this formula:
=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,2,FALSE)
where B.xls is the name of workbook 'B' and BookBDataSheet is the name of
the sheet with the information to be moved and compared. You can now
'extend' that formula to the end of the list in book 'A'.

If you will put this formula in D2 in book 'A' and extend it down to the end
of your list, it will then display "Amt Error" anytime that the amounts don't
match.
=IF(A2=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,3,FALSE),"","Amt Error")

the FALSE part of the VLOOKUP forces it to look for an exact match between
the Bill Number in 'A' and the Reference No. in 'B', but as I mentioned
earlier, the Reference No.s in 'B' must be in order from least to greatest.

This works if each Reference No in 'B' only appears once, if they can show
up more than once, then we have more work to do.

The cells in workbook B are written as $A$2 and $C$9 instead of just plain
A2 and C9 to keep them from changing as you drag/extend the formula down the
worksheet in book A.

"Asad - Shareef" wrote:

I have two files in excel that I need to use some macros or functions to
match the items. That is one of the file is file "A" contains 3 columns named
as Amout, SAP Number(which is blank) and Bill No.

The other file "B" contains 3 columns named as Reference no, Doc No. and
Amount.

I want to get Doc No from "B" file to Column SAP Number of File "A" where
the Bill No. and Amount column values of file "A" and Reference no and Amount
columns value of file "B" matches.

If Bill No. and "Reference no" matches but Amount does not match they can
appear as below or seperate worksheet so that I can investigate these.

lookup a value from multiple columns of two workbooks
 

Hi Latham
Thanks very much buddy.
Your information was very much vital in solving my queries.
Thanks again.
Rgds
Asad - Shareef


"JLatham" wrote:

This can be done with VLOOKUP() but a little more information is needed.

In file 'B', can the Reference no (to be matched with Bill No. in A) show up
more than once in your list but with different values for the Amount? Or
will a Reference number show up only once in file 'B'?

If the Reference No. will only show up once, then these formulas should work
for you. First requirement is that the information in workbook 'B' has to be
sorted by the Reference No, with smallest at the top of the list.

In 'B' your 3 columns go from A2 to C9 (change in formula to match your
range in that book), and are laid out in the sequence you gave: Ref #, Doc #,
Amount.

In book 'A' your entries are in the same range (A2 to C9).

You should have both workbooks opened up when you initially set up these
formulas, but after you set them up, you can close 'B' and it will still work.

In the cell for the first SAP entry (B2) put this formula:
=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,2,FALSE)
where B.xls is the name of workbook 'B' and BookBDataSheet is the name of
the sheet with the information to be moved and compared. You can now
'extend' that formula to the end of the list in book 'A'.

If you will put this formula in D2 in book 'A' and extend it down to the end
of your list, it will then display "Amt Error" anytime that the amounts don't
match.
=IF(A2=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,3,FALSE),"","Amt Error")

the FALSE part of the VLOOKUP forces it to look for an exact match between
the Bill Number in 'A' and the Reference No. in 'B', but as I mentioned
earlier, the Reference No.s in 'B' must be in order from least to greatest.

This works if each Reference No in 'B' only appears once, if they can show
up more than once, then we have more work to do.

The cells in workbook B are written as $A$2 and $C$9 instead of just plain
A2 and C9 to keep them from changing as you drag/extend the formula down the
worksheet in book A.

"Asad - Shareef" wrote:

I have two files in excel that I need to use some macros or functions to
match the items. That is one of the file is file "A" contains 3 columns named
as Amout, SAP Number(which is blank) and Bill No.

The other file "B" contains 3 columns named as Reference no, Doc No. and
Amount.

I want to get Doc No from "B" file to Column SAP Number of File "A" where
the Bill No. and Amount column values of file "A" and Reference no and Amount
columns value of file "B" matches.

If Bill No. and "Reference no" matches but Amount does not match they can
appear as below or seperate worksheet so that I can investigate these.