I have two excel worksheets containing corresponding data, that map 1:1 onto each other. I'm trying to build a search box on a third sheet whereby a user can enter a string in cell A2, and cell B2 will find that sting the first sheet, and display the corresponding value from the same cell on the second sheet.

Any ideas? I've been looking at some combination of the CELL and MATCH functions, but MATCH appears to only work with single columns, and I need to search a whole table.

Edit: The strings in the table on the first sheet are unique.

  • Are the strings on the first sheet unique? – Scott Craner Mar 6 '19 at 15:52
  • Yes, the strings are unique. – somnolentsurfer Mar 6 '19 at 16:07

In B2:


The two AGGREGATES will return first the row number then the column number of the match to the index which will return the value found in sheet2.

The Sheet2 reference should always start in A1 and end with the last column of data. The Sheet1 references should only be the size of the dataset.

  • 1
    Besure all references are absolute so the formula will work properly when copied down. Also, the match condition is missing; modify the formula in B2 and add the match condition to both denominators: was /(Sheet1!A1:Z100) change to /(Sheet1!$A$1:$Z$100=$A2) . The formula assumes unique values, otherwise the SMALL row match may not coincide with the SMALL column match. (if D3 & C4 both have the first match on value for SMALLest row & column, respectively, then SMALL ROW is 3 and SMALL COLUMN is 3 [which is C] so C3 is looked up on sheet2). – Ted D. Mar 6 '19 at 17:44
  • @TedD. yes that is what I get for typing into the answer box via my phone while in a meeting. – Scott Craner Mar 6 '19 at 17:50

For non unique values use this formula:

B2=INDEX(Sheet2!$A:$Z,AGGREGATE(15,7,ROW(Sheet1!$A$1:$Z$100)/(Sheet1!$A$1:$Z$100=$A2),1), MATCH($A2,OFFSET(Sheet1!$A$1,AGGREGATE(15,7,ROW(Sheet1!$A$1:$Z$100)/(Sheet1!$A$1:$Z$100=$A2),1)-ROW(Sheet1!$A$1),0,1,COLUMNS(Sheet1!$A$1:$Z$100)),0))

This formula finds the first match in the minimum row which had a match and returns the result.

Wrap the whole formula in IFERROR(formula,"") as needed because INDEX will display an error in the formula cell if A2 was not found.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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