Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Using Vlookup & Match
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$430 19,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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Using Vlookup & Match
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 On Oct 18, 6:45*pm, "ms.maryw" wrote: 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$430 19,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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Using Vlookup & Match
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Using Vlookup & Match
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 On Oct 19, 1:32*am, "ms.maryw" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup or index/match formula?? | Excel Worksheet Functions | |||
Help with VLOOKUP & MATCH formula | Excel Worksheet Functions | |||
Match, VLookup possible formula needed | Excel Discussion (Misc queries) | |||
Sum Vlookup / Match Formula across 8 workbooks | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |