Using INDEX / MATCH to look at multiple columns?

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.

All comments

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

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!

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)))

mcepaiti

Thank you so much, that did the trick.

Awesome! Glad it is working.

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.

@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.

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?

@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.

@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'.

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

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.

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

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