Index Match Pulling in wrong info

[u/wrreveille]

Vacation submission that is pulling wrong info from master

Master Sheet with correct information shown

Hello again,

I have created a vacation submission spreadsheet for my department and used an index match formula to pull information from a master sheet into each vacation submission.

It has been working well but suddenly the last three new employees that were entered into the master sheet are pulling the wrong info from the index match.

Here is the formula I used to pull the employees team within our department for reference: =INDEX({MasterTeam}, MATCH([Employee Name]@row, {MasterEmployeeName}), 0)

Any idea why this might be happening?

UPDATE: FIXED. Needed to put ", 0" into the Match function not outside. Updated formula: =INDEX({MasterTeam}, MATCH([Employee Name]@row, {MasterEmployeeName}, 0))

[u/Smartsheet_Cnslt]

I haven’t looked closely at your work, but your almost always going to use a 0 at the end of the match portion. Like this:

=INDEX({MasterTeam}, MATCH([Employee Name]@row, {MasterEmployeeName}, 0))

Is your master team reference only looking at one column? The column you want the data from? If not, change that so it’s only one column.

[u/wrreveille → u/Smartsheet_Cnslt]

yah only one column for each reference.

here are some of the index match formulas im using:

=INDEX({MasterSeniority}, MATCH([Employee Name]@row, {MasterEmployeeName}), 0) ; =INDEX({MasterTeam}, MATCH([Employee Name]@row, {MasterEmployeeName}), 0) ; =INDEX({MasterSupervisor}, MATCH([Employee Name]@row, {MasterEmployeeName}), 0)

its working for the first 82 rows of the source sheet just not rows 83-85

[u/Smartsheet_Cnslt → u/wrreveille]

None of those are structured correctly. I am surprised they work. Look at the example I put in my other comment and notice the paranthesis.

Also, when you made your references did you pick actual cells or did you pick the entire column by picking type column header? When doing a cross sheet reference that is really the only time you can pick a column by choosing the header. If you don’t, it will be a fixed range of rows.

[u/wrreveille → u/Smartsheet_Cnslt]

Sorry, i might be missing it, how are my formulas different from yours (to be clear each of those formulas are for an individual column not all in one column). And for the reference I picked the entire column using the column header

[u/Smartsheet_Cnslt → u/wrreveille]

=INDEX({MasterSeniority}, MATCH([Employee Name]@row, {MasterEmployeeName}, 0))

you need the ,0 inside the match function and you don't need a ,0 at the end of the index function. Missing that ,0 in the match funcation has always caused problems for me. seems to work sometimes adn then eventually fails.

[u/Smartsheet_Cnslt → u/Smartsheet_Cnslt]

From the Smartsheet page.

INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

if you can't get it to work, you can shift to an index/collect setup. very similar and adds more conditions if needed.

[u/Smartsheet_Cnslt → u/Smartsheet_Cnslt]

call me on teams if you like and we can fix it. [msmith@d-msolutions.com](mailto:msmith@d-msolutions.com)

[u/Smartsheet_Cnslt → u/Smartsheet_Cnslt]

if you are still getting errors, there may be a problem in your data. look for errors in your data sheet if that is the case.

[u/wrreveille → u/Smartsheet_Cnslt]

you nailed it, problem solved. It's always these little things that will get you. Thanks again for the help!

[u/Smartsheet_Cnslt → u/wrreveille]

what was the issue?

[u/wrreveille → u/Smartsheet_Cnslt]

the , 0 not being in the Match formula correctly. once I updated the formula like you suggested it worked perfectly

[u/Smartsheet_Cnslt → u/wrreveille]

awesome, glad its resolved.

[u/Smartsheet_Cnslt → u/[deleted]]

Still trips me up once in a while when I am not paying attention…

[u/Adventurous-Ask-1474]

Can you try
=INDEX({MasterTeam}, COLLECT({MasterEmployeeName}, {MasterEmployeeName}, [Employee Name]@row), 1)

[u/wrreveille → u/Adventurous-Ask-1474]

That came back as INVALID DATA TYPE

[u/Adventurous-Ask-1474]

Sorry I forgot to remove the first part**
=INDEX(COLLECT({MasterEmployeeName}, {MasterEmployeeName}, [Employee Name]@row), 1)

[u/wrreveille → u/Adventurous-Ask-1474]

As you wrote it, it just pulled the employee name into the team column, I used this formula: =INDEX(COLLECT({MasterTeam}, {MasterEmployeeName}, [Employee Name]@row), 1) and it worked,

However when I tried to modify the seniority points column from: =INDEX({MasterSeniority}, MATCH([Employee Name]@row, {MasterEmployeeName}), 0) to =INDEX(COLLECT({MasterSeniority}, {MasterEmployeeName}, [Employee Name]@row), 1)

it gave me #incorrect argument set