Comparing 2 sets of data to fill in gaps
 

Hi,
I have two sets of data both have names and one has a dollar value
associated with the name. Need to compare names and have the dollar valued
filled in the one without.

Match seems to be on the right track but it doesn' quite work how I'd hoped.

Thanks!!

Comparing 2 sets of data to fill in gaps
 

An Index n Match (Just the "Index" part of it that you're missing)

An example
Assume your source list is in Sheet1's cols A and B,
names in col A, amts in col B
In Sheet2,
if you have the names listed in A1 down
place this in B1: =INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))
Copy down to return the amts corresponding to the names listed in col A

This part: INDEX(Sheet1!B:B
is simply what you want returned as a result of the match.
The return col can be to the left or right of the col that's being matched

And if you need an error trap to return neater looking blanks: "" instead of
ugly #N/As for any unmatched names, use this in B1:
=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",INDEX(Sheet1!B :B,MATCH(A1,Sheet1!A:A,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Awrex" wrote:
I have two sets of data both have names and one has a dollar value
associated with the name. Need to compare names and have the dollar valued
filled in the one without.

Match seems to be on the right track but it doesn' quite work how I'd hoped.

Thanks!!

Comparing 2 sets of data to fill in gaps
 

Based on the title of the post - Suppose your data looks like this and is in
A1:B100

Fred 5
Jane
Marsha 10
Dan
....

And in the other table you have dollar associated with each name, lets say
that is in M1:N100

1. Highlihg all the B1:B100
2. Press F5, Special, Blanks
3. Don't move the cursor and type, but don't enter the following formula
=VLOOKUP(A2,M$1:N$100,2,False)
4. Press Ctrl+Enter

This will enter the formula in all the blank cells of column B. Note the A2
in the VLOOKUP formula simply refers to whatever cell is to the left of the
active cell. You will need to adjust this reference according to your data.
For example, if the first blank cell is B3, so it is also the first
highlighted cell after step 2, then the reference would be A3.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Awrex" wrote:

Hi,
I have two sets of data both have names and one has a dollar value
associated with the name. Need to compare names and have the dollar valued
filled in the one without.

Match seems to be on the right track but it doesn' quite work how I'd hoped.

Thanks!!

Comparing 2 sets of data to fill in gaps
 

Gracias!!!!

"Shane Devenshire" wrote:

Based on the title of the post - Suppose your data looks like this and is in
A1:B100

Fred 5
Jane
Marsha 10
Dan
...

And in the other table you have dollar associated with each name, lets say
that is in M1:N100

1. Highlihg all the B1:B100
2. Press F5, Special, Blanks
3. Don't move the cursor and type, but don't enter the following formula
=VLOOKUP(A2,M$1:N$100,2,False)
4. Press Ctrl+Enter

This will enter the formula in all the blank cells of column B. Note the A2
in the VLOOKUP formula simply refers to whatever cell is to the left of the
active cell. You will need to adjust this reference according to your data.
For example, if the first blank cell is B3, so it is also the first
highlighted cell after step 2, then the reference would be A3.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Awrex" wrote:

Hi,
I have two sets of data both have names and one has a dollar value
associated with the name. Need to compare names and have the dollar valued
filled in the one without.

Match seems to be on the right track but it doesn' quite work how I'd hoped.

Thanks!!