Find partial match from column A,B and fill partial match in C?

[Tacrier]

Hi there!

I have Spreadsheet 1 and Spreadsheet 2 with the same information but
Spreadsheet 2 is missing the 'Vendor Name' information that I need.
Spreadsheet 1 may also not contain the same number of rows that are in
Spreadsheet 2 but for now its' the only spreadsheet I have to look for
matching Vendor Names.

I want to use Spreadsheet 1 to find the Vendor Name based on looking at it's
related columns: Last Name, First Name and Invoice Number and Cheque Amount
and fill Spreadsheet 1 column C with the missing information.

Spreadsheet 1:

A B C
D
1 Last Name, First Name Invoice Number Vendor Name Cheque
Amount
2 Ander, Joe 10000 Smith
Enterprises 3500.00
3 Doe, Fawn 22 Lavender
Corp. 1100.00
4 Johnson, Andrew D90304-1 Lavender Corp.
200.00
4 Taylor, Tyler 0000040 Taylor & Sons
60.00
5 Smith, John 100004 Smith
Enterprises 540.00

Spreadsheet 2
A B C
D
1 Last Name, First Name Invoice Number Vendor Name Cheque
Amount
2 Taylor, Tyler 0000040
60.00
3 Smith, John 10000
3500.00
3 Lavender, Lila D90304-1
200.00
4 Lavender, Lila E90405-1
75.00



I have sorted both spreadsheets by column A.

The problem that I think might heed any simple solution is that a vendor
name can have different names related to it from column A with different
cheque amounts in column D, that is why I want to look at column A,B,D for a
match to fill in C.

I am not familiar with using macros so if there is a formula I could use,
any help is greatly greatly appreciated.

 

[Sheeloo]

One way is to insert a col in sheet 1 just before Col C and enter this in the
new Col C
=A1&B1&E1
and copy down till end of your data set

Go to Sheet2 and enter this in Col C
=VLOOKUP(A1&B1&D1,Sheet1!C,2,false) and copy down

This way you will get the vendor names from Sheet 1 where Col A, B and C
match with Col A, B and D in Sheet2...

You will get #N/A when no match is found.

You can Copy and PASTE SPECIAL|VALUE Col C in Sheet 2 when you are happy
with the results...

 

[Tacrier]

I am having trouble with the formula you suggested.

To make it a little easier, I have reformatted my information so that sheet
1 called 'Working Copy', and sheet 2 called 'Vendors' both have one col. that
combines the Last Name,First Name col., Cheque # col. and $ Amount Col. using
your 'A1&B1&E1' example.

This new column is J2:J5480 on the 'Working Copy' and C4:C4436 on 'Vendors'.

I would like to:

1. find a match between the J and C columns then

2. input the vendor name from 'Vendors' column H into 'Working Copy' column K

Hoping my explanation of what I want to do is not too confusing.

I have spent the last two days off and on trying to do this.

....Help?

 

[Sheeloo]

Column with Name (your col J on Vendors) has to be on the right of Col H
(which has the combined data). To get this copy Col H and PASTE
SPECIAL|Values in Col I then use the following formula in K2 of Working Copy
=VLOOKUP(J2,Vendors!H2:I5480,2,false)
and copy it down to the end of your data set...

If it still does not work then can you send the file to me

to get my id add @hotmail.com to to_sheeloo

 

[Tacrier]

Thank you that worked great!!

Column with Name (your col J on Vendors) has to be on the right of Col H
(which has the combined data). To get this copy Col H and PASTE
SPECIAL|Values in Col I then use the following formula in K2 of Working Copy
=VLOOKUP(J2,Vendors!H2:I5480,2,false)
and copy it down to the end of your data set...

If it still does not work then can you send the file to me

to get my id add @hotmail.com to to_sheeloo