DW Faisalabad New Version

DW Faisalabad New Version
Please Jump to New Version
  • Working with Tables
    • While there are four types of database objects in Access, tables are arguably the most ... read more
    Octber 25 2017

    Monday, 3 July 2017

    Two-column Lookup

    This example teaches you how to perform a two-column lookup in Excel. See the example below. We want to look up the salary of James Clark, not James Smith, not James Anderson.

    1. To join strings, use the & operator.



    2. The MATCH function returns the position of a value in a given range. Insert the MATCH function shown below.

    3. Finish by pressing CTRL + SHIFT + ENTER.



    Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.

    Explanation: The range (array constant) A2:A8&B2:B8 is stored in Excel's memory, not in a range. The array constant looks as follows:

    {"JamesSmith";"JamesAnderson";"JamesClark";"JohnLewis";"JohnWalker";"MarkReed";"RichardLopez"}

    This array constant is used as an argument for the MATCH function, giving a result of 3 (JamesClark found at position 3).

    4. Use this result and the INDEX function to return the 3rd value in the range C2:C8.

    .
    Posted by
    Labels: