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

Find more posts tagged with

Accepted answers

Brian_Richardson

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.

All comments

Brian_Richardson

Hi Amber - what Request Types determine whether you use Senior or Owner? Any other criteria other than Request Type?

AmberEspindola

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

Brian_Richardson

Oh perfect, then this should be very straightforward.

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

AmberEspindola

Thank you!!! That is exactly what i needed!!!

Brian_Richardson

Hooray!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.