Using INDEX / MATCH to look at multiple columns?

mcepaiti

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.

David Tutwiler

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.

David Tutwiler

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!

David Tutwiler

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

Thank you so much, that did the trick.

David Tutwiler

Awesome! Glad it is working.

PeggyLang

@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

@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

@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

@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

@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

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

PeggyLang

@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

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?