Using INDEX / MATCH to look at multiple columns?

Options

I have a sheet and within it has 5 columns of specific roles (ie manager, supervisor, director, owner, vendor) each column has the name/contact. Each row has a unique project number and these 5 roles. (along with many other columns of data)

In another sheet I have a column called "role" , the next column is "name". I am trying to select a role and then lookup the name associated from the other sheet. Each row has a unique project number to link to the other sheet.


Do I need to INDEX each role or is there a simpler way to write the formula?

I hope this make sense.

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    I would try something like this (this is only a partial solution):

    =INDEX({Sheet #1}, MATCH([Project Number]@row, {Sheet #1_Project Number}, 0), IF(Role@row = "Owner", 3, IF(Role@row = "PM", 4)))

    Here, the range for your index is all of Sheet #1. The first match is finding the row you need to work on. For this it will match the Project Number in Sheet #2 with the row in Sheet #1 that has the same Project Number. After that is found, it will determine which column to return in the INDEX based on the Role in your sheet. I showed 2 examples (Owner and PM). You would need to continue the IF statement for however many Roles you might have.

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    If in your INDEX MATCH you point the MATCH to the column on the 2nd sheet called "role" then write your formula to match the project number and role, you should be able to write 1 formula to capture all of the roles you're looking for.

    If you can paste what you have so far either in screenshots or with the formulas you've already written, I should be able to provide more pointers.

  • mcepaiti
    mcepaiti ✭✭✭
    Options

    Here is example of my 2 sheets:

    The formula would be in SHeet#2 in Name column - but my MATCH was trying to look at the role selected and then going back to sheet#1 column header -role to get the name for the role.


    Thanks for the help!

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    I would try something like this (this is only a partial solution):

    =INDEX({Sheet #1}, MATCH([Project Number]@row, {Sheet #1_Project Number}, 0), IF(Role@row = "Owner", 3, IF(Role@row = "PM", 4)))

    Here, the range for your index is all of Sheet #1. The first match is finding the row you need to work on. For this it will match the Project Number in Sheet #2 with the row in Sheet #1 that has the same Project Number. After that is found, it will determine which column to return in the INDEX based on the Role in your sheet. I showed 2 examples (Owner and PM). You would need to continue the IF statement for however many Roles you might have.

  • mcepaiti
    mcepaiti ✭✭✭
    Options

    Thank you so much, that did the trick.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Awesome! Glad it is working.

  • PeggyLang
    PeggyLang ✭✭✭✭✭
    Options

    @David Tutwiler

    Hello David, I have found your response to the query in this thread and it is very similar to what I am trying to build, hoping you can provide a way forward for me.

    I have a worksheet with Store #'s and 'Tech on Call' and 'Supervisor on Call' for each week of the month.

    I have another worksheet (a Lookup sheet) where call centre will be able to enter; store # & date.

    I want the appropriate Tech and Supervisor to populate in the lookup worksheet.

    My dilemma is how to have the Index & Match formula canvas the appropriate column based on the date entered?

    Screenshot below is my data worksheet


    Screenshot below is my lookup worksheet; cells in orange are the inputs, cells in white are the Index/Match results.

    In this instance I need to return the Tech name for store #126 from the column 'Tech Week 9; 02/26-03/03' and the Supervisor name for store #126 from the column 'Sup Week 9; 02/26-03/03'


    I'm kinda stuck. Any help would be greatly appreciated.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    edited 03/28/24
    Options

    @PeggyLang

    Doing this dynamically would require a really long formula that probably isn't worth it, because you would have to check each week to inform what column you need to use.

    Is there any way that this lookup is just interested in the current week or even just a few weeks around the current week? Based on your response there may be a way to add a helper column to your source sheet to try to make the formula easier.

  • PeggyLang
    PeggyLang ✭✭✭✭✭
    Options

    @David Tutwiler

    Great question! Answer would be the current week. It's basically a call centre situation. A call comes in from a store indicating the need for a tech to come to site, so lookups need to be pointed to current week.

    Does that help?

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    @PeggyLang it does. It will require 1 extra column per week on your source sheet, but it should make it a lot easier to write the formula.

    On the source sheet you need a checkbox column next to each week that you can call "This Week" or something like it. You'll need a formula to check the box of the current week is represented by the week's column you're looking at. Something like "if today is between 2/26 and 3/3 then true".

    Then on the lookup sheet you can write a big nested if statement that says, "if week 1's checkbox is checked then use week 1s column, otherwise if week 2's checkbox is checked then use week 2's column..." and so on.

  • PeggyLang
    PeggyLang ✭✭✭✭✭
    Options

    @David Tutwiler Aaahhhh! im begining to see my way thru this now. thank you. I'll report back once I have this built and working.

    Some vague thoughts running around my head -

    Instead of a big nested statement is it possible to have INDEX(range name) where 'range name' is dynamic based on what week we need to search? i.e.; if today is between 2/26 and 3/3 then range name = 'Tech Week 9; 02/26-03/03' but if today is between 02/19 and 02/25 then range name = 'Tech Week 8; 02/19-02/25'.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    edited 03/28/24
    Options

    @PeggyLang I don't know of a way to do that dynamically that wouldn't also involve a nested IF statement.

  • PeggyLang
    PeggyLang ✭✭✭✭✭
    Options

    @David Tutwiler

    David, still working on this solution.

    I have installed a helper column beside each column that represents a date range and created formula such that if today's date is between A & B then check. This works.

    Now when I'm trying to look up the corresponding name that shows up when store # is present and helper is checked I am receiving 'INVALID OPERATION'. I believe there is something amiss with my IF(AND) statement.

    However, I can't see it.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Sorry Peggy, I've been out with vacation the last few days.

    My suspicion is that it has something to do with the HAS function at the end being used as the Criterion. It's hard for me to test on my end because I'm having trouble visualizing what it is doing there.

    Is it possible to just use the [Lookup Value]2 as the criterion (essentially saying "if the row in the Wk1 TOC on call column equals the store number, then return that tech")? Or are there multiple store numbers in each cell?

  • JaymeR
    JaymeR ✭✭
    Options

    @David Tutwiler

    I too have a similar situation that I can't seem to figure out. I want to have a formula look at a specific column@row and return another column's value based on two criteria being met. I would like it to look at the Campus@row and return the Contact value where the Expertise/Role="Site Manager" AND Campus@row is the same value.

    For the Site Manager column, where the Campus is Michigan I want it to return the value of Julie, where the campus is Ohio I want it to return the value of Ethan, and where the Campus is Indiana I want it to return a value of Jasmine.

    It feels like this should be simple but I cannot for the life of me figure it out!

    -Jayme

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    edited 06/08/24
    Options

    @JaymeR,

    This formula should do it, but it does require you use COLLECT instead of MATCH so I'll explain the parameters and then attach a screenshot of it working on my test sheet.

    Formula

    =INDEX(COLLECT({Contact}, {Campus}, CID@row, {Role}, "Site Manager"), 1)

    Formula Description

    The first parameter is the range that you want to return when a value is found. So this should point to your contact column. The second parameter is the column for your first criteria that has to be met. I started with the Campus and had it look at a cell with the different states in them so the formula can be dynamic. The 4th parameter is the Role and since it is static you can just enter the text exactly as it is typed in your sheet. The 1 as the last parameter in the INDEX just tells it to return the entire COLLECT.

    So, in plain text it says exactly what you're looking for. "Return to me the Contact where the Campus matches my State cell and the Role is Site Manager.

    Testing Sheet with Test Data

    Testing Sheet with Formula Returning the Correct Results

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!