CODEWITHSUNDEEP
excelif-statementexcel-formulapivot-tablevlookup
user2682287
user2682287

Reputation: 11

IF Statement to return specific values in certain order

I am trying to run an IF Statement to return specific values in a certain order. I have three columns:- Partner_Number_Col, Email_Col, Contact_Col. One Partner Number can hold (match up to) several different Contacts/Emails. There are several different contact options in Contact_Col:

  1. Finance_Contact
  2. Main_Contact
  3. Blank_Contact (Specific Email addresses under Email_Col correspond to each of these options)

I wish to look up the partner number (from Partner_Number_Col), and then return the Email (from Email_Col) that corresponds with (Contact_Col), in the order of:

  1. Finance_Contact (If this does not exist then...
  2. Main_Contact (If this does not then...
  3. Blank_Contact

Ie. If Finance_Contact is available under Contact_Col then I want this to be returned first, Main_Contact, then blank...

Edit to insert image from link in comment:

SO18339915 question example

Upvotes: 0

Views: 372

Answers (2)

pnuts
pnuts

Reputation: 59485

If sorting is not convenient I'd suggest a PivotTable:

SO18339915 example

and merely (having selected the appropriate Partner_Number in the PT): 

=IF(A1=B3,B6)

(with a rather contrived use of =IF to comply with the stated requirement!)

This will display multiple e-addresses for the same Partner_Number and Contact combination, if present.

Revised to incorporate image in line with revised data sample:

SO18339915 second example

If source data is already in a PT cache there is no need to create a new PT (indeed better not to - if necessary just copy) but this might complicate 'filling in the gaps' because it is a requirement that each row has a Partner Number. In cells the gaps can quickly be filled by selecting the column, Home > Editing - Find & Select, Go To Special..., select Blanks (only) and =, Up, Ctrl+Enter.

Upvotes: 1

Jerry
Jerry

Reputation: 71568

If you can sort your data, just sort by Contact_Col so that all the Finance_Contact get on top, Main_Contact get just under and the blank ones go to the bottom (if the option is actially blank, you could get a column next to Contact_Col where you put 1 for Finance_Contact, 2 for Blank_Contact and 3 for Blank_Contact and sort by this column; ascending)

Then simply do a VLOOKUP on this:

=VLOOKUP("P03566453", A:B, 2, 0)

Since we sorted the data, the Finance_Contact gets at the top and if there's none, the next one is Main_Contact, and same for Blank_Contact.

It will display #N/A if the partner number cannot be found in the list.

If you have typed the partner number in cell E5 for example, you can easily do:

=VLOOKUP(E5, A:B, 2, 0)

Upvotes: 0

Related Questions