Filtered LOOKUP or MATCH based on One Value

Mike Buckiewicz

I have a master sheet of all projects that house a lot of critical information. I continually have clients ask what the status is of their projects which this sheet houses daily. I want to take select cells based on the Reference Sheet but qualify the value based on the client company name.

I am new to the MATCH formula so need some basic guidance on how to get this to work. This is what I tried but obviously not right:

=MATCH({Project Oversight Range 2}, (HAS(@cell, "Minneapolis Glass")))

Mike Buckiewicz

Also tried this following the instruction but did not help:

=MATCH({Project Oversight Range 1}, {Project Oversight Range 2}, (Minneapolis Glass))

Paul Newcome

Are you able to provide some screenshots with mock data to show exactly what you are trying to accomplish?

Mike Buckiewicz

This is the field I want to fill with the below field's information based on the Clients name field being hte qualifier:

Paul Newcome

You are going to want to use an INDEX/MATCH similar to...

=INDEX({Range To Pull From}, MATCH(Client@row, {Range To Match On}, 0))

Mike Buckiewicz

Sorry for my unfamiliarity but I entered this with the reference sheets and I know I was missing something. Where do I fill in the value for the client name to pull from?

=INDEX({Project Oversight Range 1}, MATCH(Minneapolis Glass @row, {Project Oversight Range 2}, 0))

I cant see where I am not making the connection

Mike Buckiewicz

So I got it to work after messing with it, my follow up is if there are multiple entries under the same name will it pick up those entries per the qualifier? If I copy this down it inputs the same value so I am assuming I am not supposed to do that

=INDEX({Project Oversight Range 1}, MATCH("Minneapolis Glass", {Project Oversight Range 2}, 0))

Paul Newcome

The "Client@row" portion of the formula I provided was intended to be a cell reference so that you do not have to "hard code" "Minneapolis Glass". Leaving it as Client@row will read whatever you put into that particular cell and adjust accordingly as you change that cell. If you have the formula on multiple rows, then it will read the data from whatever row the formula is in. It is basically a dynamic cell reference.

That is also why it is going to keep pulling the same value. Because it will always be matching on "Minneapolis Glass" instead of updating on each row.

INDEX/MATCH will only pull a single value. If you wanted to pull a string together based on a match, you would need a JOIN/COLLECT.

=JOIN(COLLECT({Project Oversight Range 1}, {Project Oversight Range 2}, Client@row), ", ")