Search in two columns in Excel

This example will show you how to search two columns in Excel. Look at the example below. You want to find the salary of an employee named James Clark.

  1. To concatenate strings, use the “&” concatenation operator.
  2. Function MATCH (MATCH) returns the position of a value within the specified range. Paste a function MATCH (MATCH) as shown below and press Ctrl + Shift + Enter.

    =MATCH(F2&F3,A2:A8&B2:B8,0)

    =ПОИСКПОЗ(F2&F3;A2:A8&B2:B8;0)

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.

  1. Explanation:
    • Range (array of constants) A2:A8&B2:B8 stored in Excel memory, not in the cells of the worksheet.
    • It looks like this:

      {«JamesSmith»; «JamesAnderson»; «JamesClark»; «JohnLewis»; «JohnWalker»; «MarkReed»; «RichardLopez»}

    • This array of constants is used as an argument to the function MATCH (MATCH), giving a result of 3 (the value “JamesClark” is the third in the list).
  2. Use this result and function INDEX (INDEX) to return the desired value from the range S2:S8.

    =INDEX(C2:C8,MATCH(F2&F3,A2:A8&B2:B8,0))

    =ИНДЕКС(C2:C8;ПОИСКПОЗ(F2&F3;A2:A8&B2:B8;0))

Leave a Reply