Search code examples
excelexcel-formulaexcel-2016accounting

Use records as keys to populate data in Excel?

I have two sheets in Excel. One with record ID and the other with record ID and numerical value. However, the records are not (and will never be) in the same order. How can I tell excel "Match the record ID and pull over the value to the corresponding ID on the other sheet" without manually keying everything?

It would be easy in access or a database, but my employer doesn't use any of that.

Solution

  • VLOOKUP seems like what you want.

    =VLOOKUP([Key Cell],[Lookup Range],[Column Number to Return],FALSE)

    • The [Key Cell] is the value you want to look up from the corresponding table.
    • The [Lookup Range] is the range of Cells you want to search for. The key you're using to look up must be the first column in the range. You should generally use absolute ranges here so it doesn't roll down as you copy it to cells below.
    • The [Column Number to Return] in the position of the column you want back in the range, 1 is the first (and would just return the key value if found) 2 is the second, and so on.
    • The FALSE is an "approximate match" flag. Without this, which defaults to TRUE, Excel will match the closest to your value which requires the data to be key sorted descending.

    Assuming your data is in Sheet1!A1 and your data in Sheet2!A1:B50, this would work pasted into B2.

    =VLOOKUP(A1,Sheet2!$A$1:$B$50,2,FALSE)