Filtered LOOKUP or MATCH based on One Value
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")))
Answers
-
Also tried this following the instruction but did not help:
=MATCH({Project Oversight Range 1}, {Project Oversight Range 2}, (Minneapolis Glass))
-
Are you able to provide some screenshots with mock data to show exactly what you are trying to accomplish?
-
This is the field I want to fill with the below field's information based on the Clients name field being hte qualifier:
-
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))
-
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
-
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))
-
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), ", ")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!