VLOOKUP

[Kathrynlewis]

I think I need to do a V-LOOKUP to compare the values of column A to the values of column B. I need to find the value that do not match. How do I do a VLOOKUP?
My problem is set up below:

COLUMN A Column B
ROW (1) VALUES SUBTOTAL
***** ****** ********
2 135 135
3 170 170
4 .50 1.25
5 15 15

 

[ETID]

In col C....

fill down this formula

=IF(COUNTIF(B:B,B2)>0,"Record has match in Col. B","No match&quot

Edit the "statements" as needed...

 

[ETID]

If you need a record by record comparison...then


=if(a2<>b2,&quot;No match&quot;,&quot;Has match&quot

 

[flaniganmj]

A VLookup returns the value of given reference within a matrix of numbers based on the leftmost column which MUST be in asending order. I don't know if that is what you really what to use. For instance:

=VLookup(4,$A$1:$B$4,3,False)

returns 1.25 (assuming the rows you stated were the left column)

So the syntax is:

=VLookup(Vertical Reference, Matrix of Numbers, Column in matrix, Return Value)

The last arguement is tells Excel to find an exact match, had it been set to 'True', if an exact match wasn't found, then next best thing would be returned.

Let me know if this helps, but I think you will need some sort of code to scroll through your data to return what you are looking for.

flan

 

[bkpchs237]

Kathrynlewis,

To answer your initial question on how to perform a vlookup function you would do the following. First, I'd suggest that you sort each column individually to make the &quot;check&quot; easier. Then the function in column C to check for matches for items in Column A with those in Column B would be something similar to the following:
=VLOOKUP(A2,$B$2:$B$16,1,FALSE)
where A2 is the first cell looked for and $B$2:$B$16 is the range of values within the B column. Copy the formula down the entire column. This function returns #NA if no value is found, otherwise it returns the value looked for initially.

If you want to modify the formula to display something else, such as blank if not found and the words &quot;match found&quot; then you would modify the formula like so:
=IF(ISNA(VLOOKUP(A2,$B$2:$B$16,1,FALSE)),&quot;&quot;,&quot;match found&quot

Hope this helps.

 

[acron]

You could create a 3rd Column (say Match) and enter the folowing in C2 and copy down :

=IF(ISNA(MATCH(A2,B2)),&quot;No&quot;,&quot;Yes&quot.

That works if Ax matches Bx. If you want to check each value in A against all values in B use :

=IF(ISNA(MATCH(A2,B:B)),&quot;No&quot;,&quot;Yes&quot

A.C.

 

[ETID]

Aint that what I said.... ;-)

 

[Kathrynlewis]

Just wanted to thank everyone for their help. For this case, I was actually able to just use the match/unmatch formula. thanks again.