Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match to find value on worksheet
I am importing an XML file into a workbook. I then need to find
"bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match to find value on worksheet
Since it returns just one value, Match needs a one-dimensional array, yours
is two-dimensional. Here is a UDF that might help you. In A1 I have the word "Cherry" and on Sheet2 I have many words in the range A1:D100. The UDF tell me the address of the cell holding "Cheery" when called with =WHEREIS(A1,Sheet2!A1:D100) Function whereis(myword, myrange) For Each mycell In myrange If mycell.Value = myword Then whereis = mycell.Address Exit Sub End If Next End Function To get R1C1 style use mycell.Address(ReferenceStyle:=xlR1C1) Unfamiliar with VBA? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dkline" wrote in message ... I am importing an XML file into a workbook. I then need to find "bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match to find value on worksheet
I added "Exit Sub" as an afterthought; it should, of course, read "Exit
Function" -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dkline" wrote in message ... I am importing an XML file into a workbook. I then need to find "bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match to find value on worksheet
Before I got your last reply, it had occured to be it should be End Function.
And then it worked. Many thanks for your help. It does precisely what I needed. "Bernard Liengme" wrote: I added "Exit Sub" as an afterthought; it should, of course, read "Exit Function" -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dkline" wrote in message ... I am importing an XML file into a workbook. I then need to find "bridge_CompanyName" and "value_GuaranteedCashValue". The trick is I don't know from one XML to the next where these keywords will be. I'm trying to use the MATCH function on another worksheet to tell me where these two keywords are on the worksheet. One example has the "bridge_CompanyName" in R2C7 and "value_GuaranteedCashValue" in R42C9. The next example has the "bridge_CompanyName" in R9C11 and "value_GuaranteedCashValue" in R50C13 I'm using the MATCH function. =MATCH("bridge_CompanyName",Sheet1! R1C1:R972C25,0) and =MATCH"value_GuaranteedCashvalue",Sheet1! R1C1:R972C25,0) but both are returning #N/A. All three match_type options return #N/A. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find an exact match and go to that match | Excel Discussion (Misc queries) | |||
Where is "open/tools/find/find files that match these criteria"? | Excel Discussion (Misc queries) | |||
Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
Using Index & Match functions to find data on separate worksheet. | Excel Worksheet Functions |