Formula Using Vlookup & Match

[ms.maryw]

I have two worksheets, I am trying to match the value in two cells to
two values on the next sheet. I only need to know if it is a match.

I tried:
=VLOOKUP(A3,B!$A$2:$L$43019,MATCH(B3,B!$A$2:$L$43019,0),FALSE)

This returns #N/A
A3 has an account number & B3 has a dollar amount. If the account
number matches the dollar amount on the second worksheet (B), I need
to know it's a match.

The account number could appear multiple times for different dollar
amounts.

Thank you

 

[Pete_UK]

I think it would be easier to use a helper column on sheet B (eg in
column X - this could be hidden if needed), and in this column you
could concatenate the account number (assume in column A) with the
dollar amount (assume in column D), so in X2 you could have a formula
like this:

=A2&"_"&D2

and copy this down as far as necessary. Then in your other sheet you
can have a formula like this:

=IF(ISNA(MATCH(A3&"_&B3,B!X:X,0)),"no match","match found")

Hope this helps.

Pete

 

[ms.maryw]

I like your idea, I must have did something wrong.

On sheet B I added

=B2&"_"&K2

On sheet A I added

=IF(ISNA(MATCH(A4&"_"&O4,B!$A$1:$M$43019,0)),"No Match","Match Found")

No matches were found.

Thanks, Mary

 

[Pete_UK]

Hi Mary,

MATCH will only work in a single column. Which column in Sheet B did
you put the first formula in? That is the column you need to refer to
in the MATCH term (in my example it was column X).

Hope this helps.

Pete