# Excel formula: can MATCH recognise 'n'&“01”, or 'n'&“02 ”

I have an Excel sheet (source) that has simple ID numbers in column A (01 to 40000). In another sheet (child) I have these same ID numbers in column A but with either an additional 01 or 02 added on; e.g. 0101 or 0102, 250001 or 250002, etc. Therefore this list of ID numbers is nearly twice as long. In column B there are figures.

I'm trying to extract the data from column B in the child sheet, and based on whether it has a "01" or a "02" place the figure into either column B or C of the source sheet.

My idea is to use INDEX/MATCH, but I'm not sure how the match would be written to take into account the NOT EXACT MATCH of the lookup value. MATCH(A1&"01",child!A1:A100000,).

Any tips and links greatly appreciated.

Mike.

-

It should work, but will rely on matching data types.

If the data in `child` is numeric this won't work, because `n&"01"` is a string. In which case you need to wrap your `A1&"01"` in a `VALUE()` formula to convert it to a number in every place that it is used.

Without further details, I've left this out of the rest of the answer, but it's simply enough to add it in.

If you can sort the data in the second sheet, you should be able to do this with a straight `VLOOKUP`, as such:

``````=VLOOKUP( A1 & "01" , child!\$A\$1:\$A\$10000 , 2 , FALSE )
``````

This will return the value of column `B` from the matched row, if a `###01` match is found, or `#N/A` otherwise. So you will need to wrap it in an `IF` to handle this (example with line break for readability):

``````=IF( ISNA( VLOOKUP(A1&"01",child!\$A\$1:\$A\$10000,2,FALSE) ) ,
"" , VLOOKUP(A1&"01",child!\$A\$1:\$A\$10000,2,FALSE) )
``````

That formula will do you for column `B`, you need the same but with `&"02"` in column `C`.

-
I've put this together but I'm receving a #REF: =IF(ISNA(VLOOKUP(VALUE(A3&"01"),'[Test - individual data (2).xls]Sheet1'!\$A\$3:\$A\$188,2,FALSE)),"",VLOOKUP(VALUE(A3&"01"),'[Test - individual data (2).xls]Sheet1'!\$A\$3:\$A\$188,2,FALSE)). Any ideas why? – RocketGoal Jan 14 '11 at 13:39
`#REF` implies it cannot find a cell you are referencing. At a guess the sheet specifier is upsetting it, so try using the formula on the same sheet (temporarily!) to see if this helps. Also, try the "Evaluate Formula" function (off the top of my head, I'm not sure where it is on Excel2007's ribbon), you can see step-by-step what's happening and which bit is failing. – DMA57361 Jan 14 '11 at 13:55
Thanks. Can't believe I didn't try your first suggestion before I posted my call for help! Live and learn. – RocketGoal Jan 14 '11 at 14:16
Now getting a blank cell: =IF(ISNA(VLOOKUP(A2&"01",\$E\$2:\$E\$15,6,FALSE))," ",VLOOKUP(A2&"01",\$E\$2:\$E\$15,6,FALSE)). A2 is IDSource and range E:E is IDChild. Column 6 (F) is where the figures should be taken from . – RocketGoal Jan 14 '11 at 15:50
If you want to return `F` then you need to change `\$E\$2:\$E\$15` to `\$E\$2:\$F\$15` (ie, it should include `F`) and then the column number is `2` (because the column being checked - `E` in this case - is taken as `1`). Also, note that the data the `VLOOKUP` is checking (\$E\$2:\$E\$15) in this example, must be sorted in decending order on the first column. – DMA57361 Jan 14 '11 at 16:01

In B1:

``````=IF(ISNA(MATCH(A1&"01",child!\$A\$1:\$A\$10000,FALSE)),"",VLOOKUP(A1&"01",child!\$A\$1:\$C\$10000,2,FALSE)
``````

In C1:

``````=IF(ISNA(MATCH(A1&"02",child!\$A\$1:\$A\$10000,FALSE)),"",VLOOKUP(A1&"02",child!\$A\$1:\$C\$10000,3,FALSE)
``````

and fill down as needed.

-

It would seem the names for your sheets are mismatched. Functionally your child sheet is actually the source. Using your names however:

Keep it simple. Use vlookup. These equations will pull data into the "source" sheet:

In column B:

``````=vlookup(a4&"01",child!a\$4:b\$13,1,0)
``````

In column C:

``````=vlookup(a4&"02",child!\$a\$4:b\$13,1,0)
``````

This assumes data starts in row 4 of both sheets. You will need to expand the data range `child!a\$4:b\$13` to encompass all your data on the child sheet. Then fill the equations down.

Some error handling may be required to control `#n/a` behavior:

``````=if(isna(vlookup(a4&"02",child!a\$4:b\$13,1,0)),"",vlookup(a4&"02",child!a\$4:b\$13,1,0))
``````
-