Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003

    Unanswered: Searching across file...PLS HELP!

    i have an excel file (file1.xls) with 2 columns (first name) & (last name) and another file (file2.xls) with 3 columns (first name) , (last name) & (Age).

    pl suggest a way to search the combination (first name & last name) from file1 in file2, retreive the corresponding Age from file2...and place it in file1.

    no 2 combinations will be same.


  2. #2
    Join Date
    Jul 2003
    London UK
    I'm typing out the answers longhand here as I don't have a copy of Excel installed on this PC but I think they should work...

    It depends how many rows you're talking about. You can use array formulas, a sub-search using sorts, INDEX and MATCH, a hack containing a hidden concatenation column, or code. Ideally all solutions should be through Excel (ie visible) as you don't know whether you're going to be passing your model onto geniuses or dummies.

    Let's say you have WS1 and WS2 with columns A through C being FName, SName and Age, and Age is populated in WS2. Let's assume that you have 1000 rows in WS2.

    the following uses r1c1 notation...

    My preferred choice (not for purists, but it's easiest to maintain and explain to dummies) would be to insert a column into WS2 at A (which could subsequently be hidden) with the formula =RC[1]&RC[2], and a formula in column C of WS1 with the formula =if(isna(vlookup(RC[-2]&RC[-1],WS2!C1:C4,4,false)),"Not Known",vlookup(RC[-2]&RC[-1],WS2!C1:C4,4,false))

    If you went for array formulas, you're mentally thinking about adding up totals rather than looking things up, so you're probably thinking in terms of:
    =sum(if(RC1=WS2!R1C1:R1000C1,if(RC2=WS2!R1C2:R1000 C2,WS2!R1C3:R1000C3,0),0))
    Hit Ctrl+Alt+Enter to make this an array formula then you've got it. Basically it's like putting in an extra column on the second spreadsheet, and uses as much memory, but if you're trying to be neat then this might be an option. It does have its memory limitations though.

    Yet another option would be to sort WS2 by SName then FName, and use the different options of MATCH (1,0,-1) to return the row number of eg the start and the end of the Smiths. This then gives you your search range to look for John, Jane or Ebeneezer in column B. Here you'd have to use INDIRECT so that you could put the results of your first searches into a searchable range.

    Using code the simplest option would be to sort WS2, find the surname in column A on WS2 to return eg lngRow, then find the first name in column B using the parameter 'after:=WS2!range("B"& lngRow-1)', then offset(0,1) to the correct result.

    Or you could run a loop program without sorting to find first the surname then the firstname, but this will undoubtedly be veeeerrrryyy ssslllloooowwww. But it would have the advantage of being able to be put into a formula you could then call from the worksheet.

    There are also other options using specialcells.... but I think this may be enough for you to go on.

    I'll be back on the proper PC tomorrow so will be able to demonstrate if required.

  3. #3
    Join Date
    Jul 2003
    thanks a lot for ur solution, i already implemented the concatination method by myself...but ur solution looks perfect too.


Posting Permissions

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