I need a formula to return information with multiple criteria.
I have looked through a TON and can not figure this one out. The request types determine what level needs to handle it (more testing column), if it is the owner of the account or senior level of the account. The customer details on a different sheet that hold the information on who the senior and owner is per the customer. I'm trying to have it pull the information using the customer number and knowing which column to pull the name from, ie either the owner or senior.
i just can't seem to get any of them to work.
any help on this would be greatly appreciated.
customer number, either owner/senior columns to return person's name
Best Answer
-
Oh perfect, then this should be very straightforward.
In the column where you want the Owner or Senior contact to appear, use this formula. Set it as a column formula (right click, Convert to Column Formula) so that it runs for all rows regardless of changes to the rows.
= IF([Owner/Senior Level]@row="Owner", INDEX({Owner}, MATCH([Customer Number]@row, {Customer Number}, 0)), INDEX({Senior}, MATCH([Customer Number]@row, {Customer Number}, 0)))
This formula assumes that it's either Owner or Senior.
The {} bracketed references are cross-sheet references that point to your detailed customer info sheet. You create them when you're typing out this formula and click "Reference Cells from Another Sheet" in the popup formula helper box. Then browse to the detailed sheet and select the appropriate column.
Answers
-
Hi Amber - what Request Types determine whether you use Senior or Owner? Any other criteria other than Request Type?
-
The type of request determines if it is senior or owner but i have a formula that does that and puts it in a column itself.
So in this case, a cash move is what an owner would work.
this is the details for the information i need returned, so it would look at the customer number and then from the owner/senior level know which column to return with the name.
i can do it and excel but its not the same here
-
Oh perfect, then this should be very straightforward.
In the column where you want the Owner or Senior contact to appear, use this formula. Set it as a column formula (right click, Convert to Column Formula) so that it runs for all rows regardless of changes to the rows.
= IF([Owner/Senior Level]@row="Owner", INDEX({Owner}, MATCH([Customer Number]@row, {Customer Number}, 0)), INDEX({Senior}, MATCH([Customer Number]@row, {Customer Number}, 0)))
This formula assumes that it's either Owner or Senior.
The {} bracketed references are cross-sheet references that point to your detailed customer info sheet. You create them when you're typing out this formula and click "Reference Cells from Another Sheet" in the popup formula helper box. Then browse to the detailed sheet and select the appropriate column.
-
Thank you!!! That is exactly what i needed!!!
-
Hooray!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!