index 10 different columns, all with same match criteria

amschock · 02/08/23

I am looking to index 10 columns from sheet B into 10 columns in sheet A, all using the same match criteria... it is only returning the result in column A1. Like it's processing left to right and stopping after a result is returned.

Each of my 10 columns has basically this same formula, only difference is what I am indexing.

=IFERROR(INDEX({23MasterCallOutSunday4/2}, MATCH([Employee ID]@row, {23MasterCallOutEmployeeID}, 0)), " ")

Any help would be appreciated. Thank you.

Jeff Reisman · 02/08/23

@amschock

If you edit the references in your formulas, are they all pointing to the proper ranges?

Paul Newcome · 02/08/23

Yes. It will stop after the first match. Are you able to provide some screenshots for reference?

amschock · 02/08/23

@Jeff Reisman Both sheets have a column named "emp id" - that is the value that I am using for the match.

@Paul Newcome Confirmed. When I go to sheet B and delete that first match, sure enough it returns the next match. Any way around this? Any better ideas?

Thanks!

Jeff Reisman · 02/08/23

https://community.smartsheet.com/discussion/comment/361293#Comment_361293

Maybe I'm missing something from your description...

You have an INDEX/MATCH in the first column of Sheet A, looking for a value in a particular column in Sheet B, where the Emp ID for both rows matches. Then you have an INDEX/MATCH in the second column of Sheet A, looking for a value in a different column in Sheet B, where the Emp ID for both rows matches, and so on, yes? If no, then maybe a screenshot would help.

amschock · 02/08/23

@Jeff Reisman That is correct. I am trying to return 10 different results from 10 different columns, all on the same row with the same emp id match. It is stopping after the first result.

amschock · 02/08/23

Jeff Reisman · 02/08/23

An INDEX/MATCH formula in one cell shouldn't have anything to do with an INDEX/MATCH formula in a neighboring cell. (I also noticed there are no inbound cell link indicators in the Monday, April 3 column. Is there not a formula in that column?)

Here's my lookup data, "Sheet B::

Here's my Sheet A, you can see that I'm populating SheetACol1 by matching the Employee ID and pulling data from the SheetBCol1 column in the "Sheet B" lookup sheet:

And here doing the same thing with SheetACol2 looking up values from SheetBCol2 by matching EmployeeID.

When I changed values in Sheet B, the values updated in Sheet A:

amschock · 02/08/23

@Jeff Reisman There had been a formula in the Monday, April 3 column but in my frustration it had been deleted to start fresh just before the screenshot.

I agree, the formula in each column should be independent of each other but this just won't work today.

Incidentally, the emp id on sheet B is being populated by an index/match formula.... is that why this is not working for me??

Jeff Reisman · 02/08/23

https://community.smartsheet.com/discussion/comment/361307#Comment_361307

So you're populating the Sheet B Employee ID by matching it to something on Sheet A? yeah, that might have something to do with it!

Can you show some sample data and your formulas for all things involved?

amschock · 02/09/23

@Jeff Reisman I've got a lot going on here.

Sheet B: being populated by a form: first name and last name are two columns
Sheet B: emp id is being populated by an index/match formula (searching multiple sheets to return a match) based on first name/last name combination

I think I'm going to attempt something different. Is it possible to use index/match combined with an "IF"?

Similar to this, which is not working.

=IFERROR(IF({23MCO DAY/DATE} = "Sunday, April 2"), INDEX({23MCO N+R}, MATCH([Employee ID]@row, {23MCO EID}, 0)), " ")

Thank you!

Jeff Reisman · 02/09/23

@amschock I hear you! I routinely challenge Smartsheet to make it do things it really shouldn't do 😂

Something I would try would be to add a helper column into Sheet B, and copy some of the employee ID values over from that formula column. Then on Sheet A, change one of the INDEX/MATCH formulas that isn't working to match the Sheet A employee ID against that helper column in Sheet B, and see if it works.

Another thing to think about - when you create your ranges, are you selecting the entire column, or just part of the column?

As to your question, you can definitely run INDEX/MATCH (or any formula, really) as the value-if-true or value-if-false in an IF formula.

Paul Newcome · 02/09/23

Try an INDEX/MATCH, but the first range for the INDEX will be different in each column.

Column 1:

=INDEX({Sheet A Column 1}, MATCH([Emp ID]@row, {Sheet A Emp ID}, 0))

Column 2:

=INDEX({Sheet A Column 2}, MATCH([Emp ID]@row, {Sheet A Emp ID}, 0))

Column 3:

=INDEX({Sheet A Column 3}, MATCH([Emp ID]@row, {Sheet A Emp ID}, 0))

NOTE:

When creating the new ranges, do not "Edit Reference". You can copy/paste the formula then highlight the entire first range including the curly brackets. The blue text in the formula helper box should change from "Edit Reference" to "Reference another sheet". Click on that to create the new reference to the new column.

amschock · 02/09/23

@Jeff Reisman I tried the helper column on Sheet B last night, still would not run properly.

As far as ranges, I am selecting whole columns.

Index/match, IF - I added an example above in my comment... any suggestions?

Jeff Reisman · 02/09/23

https://community.smartsheet.com/discussion/comment/361410#Comment_361410

This portion in BOLD is not going to work as a logical expression, unless that {23MCO DAY/DATE} "range" is a single cell.

=IFERROR(IF({23MCO DAY/DATE} = "Sunday, April 2"), INDEX({23MCO N+R}, MATCH([Employee ID]@row, {23MCO EID}, 0)), " ")

See Paul Newcome's comment from a few minutes ago (particularly the "NOTE" part) to check that your ranges were set up correctly. Be sure that for each you are selecting an entire SINGLE column from Sheet B as your index range, and an entire SINGLE column from Sheet B as your match range.

Lastly, I would try saving a new copy of both sheets, and then recreating your range references the new copy of Sheet A to point at the columns in the new copy of Sheet B. This will rule out any crazy weird corruption behind the scenes that is preventing this from working. (I've seen it happen once or twice, where the only thing that resolved the issue was using an entirely new copy of a sheet.)

amschock · 02/09/23

@Paul Newcome

@Jeff Reisman

Friday, March 31:

=INDEX({23MasterCallOutFriday3/31}, MATCH([Employee ID]@row, {23MasterCallOutEmployeeID}, 0))

Saturday, April 1:

=INDEX({23MCO 4/1}, MATCH([Employee ID]@row, {23MasterCallOutEmployeeID}, 0))

Sunday, April 2:

=INDEX({23MasterCallOutSunday4/2}, MATCH([Employee ID]@row, {23MCO EID}, 0))

Monday, April 3:

=INDEX({23MasterCallOutMonday4/3}, MATCH([Employee ID]@row, {23MCO EID}, 0))

and so on. I took the "iferror" out of the formulas to show there is a result in the green circled columns, it is just not literally showing it to me.

index 10 different columns, all with same match criteria

Answers

Help Article Resources

Categories