Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
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.

share|improve this answer

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))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.