Index Matching

To Whom it may concern:

I am having issues calling up values from another table based on the formula below. I thought the values I used as references were unique enough but I am receiving a no match in many of the cells I am trying to reference. Please help if you can.

This table I will be referencing to populate cells in another sheet based off similar criteria with an index match formula, but this is where I am running into issues because after I pass 09 in the sorting value column it does not work. The formula is assigned as a column formula, so this is where I am just confused.

My formula for this sheet is as follows:

=INDEX({Candidate}, MATCH([Sorting Value]@row, {Booth Value}, 0))

{Candidate} references the range for Candidate (RecNo.) in the table below

{Booth Value} references the range for Booth Value in the table below

The other table is setup is structure in the picture below;

My formulas for this sheet is as follows:

=INDEX({Booth Assignment Range}, MATCH([Co-Op Time Slot(s)]@row, {Record Number Range}))

=INDEX({Interviewers}, MATCH([Co-Op Time Slot(s)]@row, {Record Number Range}))

{Booth Assignment Range} references the range for Sorting value in the table above

{Record Number Range} references the range for Record Number in the table above

{Interviewers} references the range for Interviewers in the table above

Both the Co-Op Time Slot(s) range and the record numbers will have the same values and the same goes for Booth Value and Sorting Value

Note: Sorry for the inconsistent naming conventions, kind of new to this whole formula thing.

Overall I am getting no match in a lot of cells but I should be getting a real value based on values between the two tables.

Any help would be greatly appreciated. If nothing here makes sense, please ask questions and I will try to explain a little better.



  • Davidp
    Davidp ✭✭

    Are you trying to reference a field from the first sheet or second sheet to the oposite sheet?

    I'm not seeing exact reference in both fields in your example.

    Sheet 1

    Sheet 2

    =INDEX({Sheet1 Range 2}, MATCH([Product ID]@row, {Sheet1 Range 1}, 0))

    -Sheet 1 range 2 is the description column in sheet 1

    -product ID @row is what I want the match to find in sheet 2

    -Sheet1 Range 1 is the product id in sheet 1 that i want to match against in the "product ID @ row in sheet 2

    In your example,, i'm not too sure I understand your index/match to the candidate in the first sheet. Is that information referencing another sheet. Its fine that you place the candidate information in that column; however it appears its looking something up as there are no matches in the bottom few records.

    If the candidate was complete in that specific sheet and not a lookup, your functions in the second sheet should look like this.

    Booth Assignment:

    =Index(Sheet1 assign to range, match(Candidate (rec no.) @ row, sheet 1 candidate reference,0))

    This function will index the assign to data in sheet 1 and return that data when matched to candidate @ row on sheet 2 to Candidate (rec no.) on sheet 1.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!