Hello,

   I'm hoping you can help. Here's the situation that I'm trying to solve via formula:

I have a list of specialties in column E of Sheet 1. In column F, I need to designate whether this specialty in column is is group 1 or group 2.  Groups are designated on the SpecialistTable sheet with the headers of Group 1 (columnA) and Group 2 (column B). 

Here's my theoretical formula written out here for all to critique.

In Cell F2, write "Specialist" if E2 is found in column A of SpecialistTable Sheet; write "Specialist 2" if E2 is found in Column B of SpecialistTable Sheet; write "Other" if E2 is not found at all. 

I've been trying to use a combination of IF and IFERROR and Vlookup but to no avail. A simple nested IF doesn't seem to work.

One last note, I need this to run as a part of a macro initially, but also need Sheet 1 to reflect changes/additions to the Specialist table after the macro has run. 

I'm glad to clarify if you have questions.

Thanks for your time,

Beth Singley

Use the Name Manager on the Formulas tab of the ribbon to define a named range Specialist1 that refers to

=OFFSET(SpecialistTable!$A$1,1,0,COUNTA(SpecialistTable!$A:$A)-1,1)

and a name Specialist2 that refers to

=OFFSET(SpecialistTable!$B$1,1,0,COUNTA(SpecialistTable!$B:$B)-1,1)

These named ranges are dynamic, they will grow/shrink as data are added/removed.

Enter the following formula in F2 on Sheet1:

=IF(ISNUMBER(MATCH(E2,Specialist1,0)), "Specialist 1", IF(ISNUMBER(MATCH(E2,Specialist2,0)), "Specialist 2", "Other"))

This formula can be filled down.

Hi Hans,

I got this formula working with the nested ifs, but your suggestion about the dynamic ranges was insightful for a different issue where I'm trying to match a value within an array using vlookup. 

Here is the working formula:

=IF(IFERROR(VLOOKUP(E2,SpecialistTable!A:A,1,FALSE),"")<>"","Specialist",IF(IFERROR(VLOOKUP(E2,SpecialistTable!B:B,1,FALSE),"")<>"","Specialist 2","Other"))   

Thanks for your help!

Beth

• Marked as answer by Tony Chen CHNMicrosoft contingent staff, Moderator Monday, December 23, 2013 5:07 PM

Hi Hans,

I got this formula working with the nested ifs, but your suggestion about the dynamic ranges was insightful for a different issue where I'm trying to match a value within an array using vlookup. 

Here is the working formula:

=IF(IFERROR(VLOOKUP(E2,SpecialistTable!A:A,1,FALSE),"")<>"","Specialist",IF(IFERROR(VLOOKUP(E2,SpecialistTable!B:B,1,FALSE),"")<>"","Specialist 2","Other"))   

Thanks for your help!

Beth

• Marked as answer by Tony Chen CHNMicrosoft contingent staff, Moderator Monday, December 23, 2013 5:07 PM

Hello Friends,

I have to filter Column A, Column B and Column C for different data selection and after that in Column D I have multiple dates. In the filtered dates I need to know the latest date and the relevant data of that particular date which is in Column E in the respective latest date cell. 

Please help me on this.

Uttam

That is a completely different question; please start a new thread if you have a question that is not related to the existing thread.

Will the dates in column D be distinct, or can there be duplicates?