How do I match one column of data to another when they have a different number of rows?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find a way for excel to compare two columns to each other and place the corresponding data beside the first column. Here's an example
A B C
1 .1 1 .
2 3 .
3 .5 5 .
4
5 .7
6
Basically Column B is what I am trying accomplish. Columns A, C, and D are given. B is derived by comparing C to A and Placing D beside A if A=C.
Thanks
Chad
 
Hi
enter the following in B1
=IF(ISNA(VLOOKUP(A1,$C$1:$D$20,2,0)),"",VLOOKUP(A1,$C$1:$D$20,2,0))
and copy down
 
Frank, Thanks so much for the help. I used your formula and it worked well for the first few observations, but even though I copied the formula down it quit working after about 180 cells. Here's what I have and what I did: Column A is from A2:A2491 and the formula goes in Column C and the data is in D2:E110 my formula is:=IF(ISNA(VLOOKUP(A2,$D$2:$E$110,2,0)),"",VLOOKUP(A2,$D$2:$E$110,2,0)

Thanks
Chad
 
Hi Chad
what happened exactly after you copied it to many cell?
- did an error occur
- wrong results

--
Regards
Frank Kabel
Frankfurt, Germany

Trucks 1975 said:
Frank, Thanks so much for the help. I used your formula and it
Click to expand...
worked well for the first few observations, but even though I copied
the formula down it quit working after about 180 cells. Here's what I
have and what I did: Column A is from A2:A2491 and the formula goes
in Column C and the data is in D2:E110 my formula
is:=IF(ISNA(VLOOKUP(A2,$D$2:$E$110,2,0)),"",VLOOKUP(A2,$D$2:$E$110,2,0)
)
 
The formula copied fine. Where it didn't find a match there was no value, and where there was a match the correct number was there (for two observations). However, when it quit working there was just no value as if there wasn't a match, but in reality there was.
 
Hi
in most cases there isn't an EXACT match. Probably some spaces or
invisible characters in the matching range. Also check if automatic
calculation is enabled
 
Frank

That was the problem. I made column a with a formula and most of the values looked like this: 185.4999999999999999999999999999. My computer (brain) automatically rounds this up. Anyhow, I made them whole and it works perfectly now. Since you're so good at this, let me ask one more question. Now that I have matched the values up correctly I need to condense the matches to a group of cells. Is there a way to do that? I have Columns A, B, & C (with your formula) and C only gets a value every 50 or so cells. So I'm looking for a condensed version with A, B, & C without all of the missing cells, and of course the data in Rows A & B when there is no C isn't important to me.

Thanks

Chad
 
Back
Top