NEED HELP WITH INDEX/MATCH! Tried EVERYTHING!

I am really struggling with getting the INDEX formula to work, and I'am not sure where the hiccup is.

I have a sheet "Property Metadata" that I would like to autofill the columns based off of the "Prject ID" cell.

When I enter the "Project ID" in sheet "Property Metadata", I would like it to reference the "Project ID" column in my second sheet "Portfolio Property Intake Sheet" and return the data from the appropriate column (Property Name, Property Address, etc.)


I feel I have tried everything under the moon and am really stumped.

This is the sheet that holds all of the data:

This is the sheet that I would like to autofill based off of the Project ID:

Thank you in advance to anyone that can help!!!!

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @FMV,

    Your INDEX MATCH should be along the lines of this (using Property Name as an example):

    =INDEX({Property Name}, MATCH([Project ID]@row, {Project ID}, 0), 1)

    The Property Name cross sheet range:

    The Project ID range:

    Original data:

    Result:

    For the other columns, you would change the first cross sheet reference in your INDEX formula to the relevant column in the intake sheet.

    Hopefully this helps, but if you've any more problems/questions then just post! 🙂

Answers

  • Razetto
    Razetto ✭✭✭✭✭✭

    @FMV Hi, can you show your formula? I'll guess something like this =INDEX([Property Name],MATCH([Property ID]@row,{Portfolio Property Intake Sheet Range 1}, 0)) where the range 1 is the property ID column in your Metadata sheet

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @FMV,

    Your INDEX MATCH should be along the lines of this (using Property Name as an example):

    =INDEX({Property Name}, MATCH([Project ID]@row, {Project ID}, 0), 1)

    The Property Name cross sheet range:

    The Project ID range:

    Original data:

    Result:

    For the other columns, you would change the first cross sheet reference in your INDEX formula to the relevant column in the intake sheet.

    Hopefully this helps, but if you've any more problems/questions then just post! 🙂

  • FMV
    FMV ✭✭

    @Nick Korna YOU ROCK!!!!! That worked perfectly and your explanation was so easy to follow. Thank you for your help!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem. Some of the functions can be confusing at first until you get your head round them and then you can reel them off in no time without any hassle.