Index Match is it the right tool for this job?

[wlambeth]

I have a customer list of 14,000 rows of data and I'm trying to match the balance for those customers to an AR Aging table that only has 3,000 rows.
Our customer list has many duplicate names due to having multiple billing addresses for the same customer.
So to get a more refined match I have to reference the customer number as well as the customer name and return the dollar value that corresponds to those two values.

I tried a vlookup but that only allows me to choose one value to search for.
Since there are multiple duplicate names I have the same value replicated many times.

This is my Cust List
Cust# Cust Name AR bal Inv Date
1234 xyz Corp ?????? ???????
I would like to return the AR balance and invoice date from my AR aging table for the customer and name match.

My Aging table is
Cust # Cust Name Inv Date AR Bal

My attempts to get this to work so far have failed.

Any help will be greatly appreciated.

 

[steve the fish]

So are you saying the sum of the AR Bal for the 14000 row for each customer should equal the AR Bal for each customer in the smaller table? If so take a look at SUMIFS. Place it in a spare column at the end of the smaller table and use that smaller table for the criteria.

 

[wlambeth]

My special challenge is the following
My AR aging table has cust# 4 with cust name of XYZ-Corp Plano
My Cust list table has cust # 4 with cust names of XYZ Corp-plano, XYZ Corp-Houston, XYZ-Corp-Austin.
Each formula I have tried returns the value for Cust# 4 for each of the cust names.
In this case tripling the amount owed, when the only one that should be returned is the value for Cust#4 for cust name XYZ Corp-Plano.

 

[steve the fish]

You said you had a customer number? Use that. I presume its unique to the customer. The customer name field is no use to you if its inconsistent.

 

[wlambeth]

Thank you Steve the Fish!