Help with VLookup to populate data from one sheet to another based on a match in same-name column

Options

I have an account database with facility location address information. When the same facility is entered in the order sheet, I need a lookup function to reference the database sheet and then populate the address.

This is what I have so far but I get a # NO MATCH error

=INDEX({Customer Database Range 1}, MATCH([Building / Facility]@row, {Customer Database Range 2}, 0))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 03/04/22
    Options

    Hey @stkatch

    Your syntax for the INDEX/MATCH appears correct. With the generically named cross sheet ranges, I can't tell what your ranges are. (You can change the range names to reflect your actual column names).

    Is it possible that your Range 1 and Range 2 are in the wrong place?

    The INDEX range should be the address range. The Match range should be your Facility range. Are these the order of your ranges? To trouble shoot, delete Range 1 completely from the formula and re-insert it by going to the referenced sheet and insert the reference. Then, completely delete range 2 from the formula and reinsert it by going to the referenced sheet and insert the reference. As you are re-inserting each range, make sure that column is the correct column and that the entire column is selected.

    And last trouble-shooting questions- the [Building / Facility]@row for the row in question is not blank and (2) the [Building / Facility]@row does actually have a match in the other sheet?

    Kelly

  • stkatch
    stkatch ✭✭✭✭
    Options

    THANK YOU KELLY!!! You saved me hours....swapping the ranges worked

    =INDEX({Customer Database Range 2}, MATCH([Building / Facility]@row, {Customer Database Range 1}, 0))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!