# Thread: Matching number with a given particular text

1. ## Matching number with a given particular text

Hi,

Is there any function that will help in matching the below conditons

1) Match Trans Type Transfer In - Inspecie with Transfers In with same amount

2) Match Trans Type Repurchases with Sell with same amount

3) Match Trans Type Buy with Sales with same amount

2. And what your expected results? Maybe more clear describe it at your file

Thanks

3. Maybe this array​ formula

=IFERROR(INDEX(\$D\$2:\$D\$19,MATCH(-C2,IF(\$E\$2:\$E\$19=INDEX({"Sales","Sell","Transfers In"},MATCH(E2,{"Buy","Repurchases","Transfer In - Inspecie"},0)),\$C\$2:\$C\$19),0)),"")

4. I'm getting a fail on searching the negative (buy = - sale) plus there is a rounding problem.
I inserted a D column D2 = =ROUND(C2,2)

Then the three array formulas (Cntrl Shift Enter)

H2 = IF(\$F2="Transfer In - Inspecie",ROW()+MATCH(C2,C3:C100,0),"")
I2 =IF(\$F2="Repurchases",MATCH(-D2,IF(F:F="Sell",D : D,0),0),"")
J2 =IF(\$F2="Sales",MATCH(-D2,IF(F:F="Buy",D : D,0),0),"")

Returning the row number, Use index to get whatever you want
H2 also needs an IFERROR but leaving that to you

5. [QUOTE=WizzardOfOz;14928]I'm getting a fail on searching the negative (buy = - sale) plus there is a rounding problem.
I inserted a D column D2 = =ROUND(C2,2) /QUOTE]

If he had seen the information in other posts on this site about the problem, Bob would have been able to pick up the point about matching + with - values, but this is not mentioned in post #1 here.
I think that by posting multiple questions, none of which explain the problem fully or accurately a situation similar to cross posting arises.

6. Originally Posted by WizzardOfOz
I'm getting a fail on searching the negative (buy = - sale) plus there is a rounding problem.
I inserted a D column D2 = =ROUND(C2,2)
If he had seen the information in other posts on this site about the problem, Bob would have been able to pick up the point about matching + with - values, but this is not mentioned in post #1 here.
I think that by posting multiple questions, none of which explain the problem fully or accurately a situation similar to cross posting arises.

7. Bob Phillips, WizzardOfOz & Hercules1946 I appreciate your efforts, but the thing is not working for me, please check if this can be resolved,

8. Originally Posted by dinesh.sarsar
Bob Phillips, WizzardOfOz & Hercules1946 I appreciate your efforts, but the thing is not working for me, please check if this can be resolved,

Hello Dinesh
Perhaps you could start by responding to the points I made in your other post, namely that you don't have enough information to properly automate this process. The correct way to do this is to ensure that matching records carry a common reference,exactly like the match codes that you are allocating manually (1,2,3 Etc). However, this must be done when the records are created, so that you aren't faced with a lot of unmatchable data when the records are eventually brought together.
To illustrate the matching problem, look at rows 2 and 3 in your workbook "Deals.xls" in Post #1.
Manually its easy to determine that these rows match with rows 12 and 13, but its impossible to say which is which without more information. You may say that for your purposes that doesn't matter, but others using the information may not agree, and after all there will only be one correct way to match them.
Despite both sets of records carrying detailed references, there is no commonality in any of them.
You need to address this issue if you want to automate the process, and resolve the errors that you must be making with your manual matching.

9. I inserted a round column in D to get rid of the non matching and negative issue. Then I added 3 columns and return the row number for each match. If you add
index( column to return, my formula) it will return your answer but as Hercules points out I don't know what you want to see, date / ref etc

10. Originally Posted by WizzardOfOz
I inserted a round column in D to get rid of the non matching and negative issue. Then I added 3 columns and return the row number for each match. If you add
index( column to return, my formula) it will return your answer but as Hercules points out I don't know what you want to see, date / ref etc
When two matching records are found, the OP needs to add a common numeric value to each, starting with 1, and using the next available number for the next match.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•