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([CoOp Time Slot(s)]@row, {Record Number Range}))
=INDEX({Interviewers}, MATCH([CoOp 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 CoOp 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.
LaBeach
Answers

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
Categories
Check out the Formula Handbook template!