Nested IF/AND with VLOOKUP

**Shanebass** · 11-22-2017, 12:50 PM

Hello Everyone.

I am having trouble with a Nested IF/AND statement with VLOOKUP. Here is the function <=IF(AND(Sheet5!G4="Access CDS",Sheet2!$B39,Sheet2!$E39),VLOOKUP(Sheet2!$A39,Sheet2!$A39,1),IF(AND(Sheet5!G4="Transfer CDS",Sheet2!$C39,Sheet2!$E39),VLOOKUP(Sheet2!$A39,Sheet2!$A39,1),IF(AND(Sheet5!G4="Multilevel CDS",Sheet2!$D39,Sheet2!$E39),VLOOKUP(Sheet2!$A39,Sheet2!$A39,1),"")))>

So Sheet5!G4 is a drop down box. What I am trying to do is if Sheet5!G4 shows Access CDS and Sheet2!B39 and Sheet2!E39 evaluate to True, show Sheet2!A39, Else IF Sheet5!G4 shows Transfer CDS and Sheet2!$C39 and Sheet2!$E39 evaluate to True, show Sheet2!A39....and so on. Sheet5!G4 is a drop down list.

I'm new to Excel but do have some programming knowledge although it has been quite a while...

Thanks in advance for your help.

**JohnTopley** · 11-22-2017, 12:57 PM

VLOOKUP(Sheet2!$A39,Sheet2!$A39,1)

This doesn't make sense as your looking up the same value you are returning: in fact, you are returning the same value, the only variable being B39,C39,D39

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Shanebass** · 11-22-2017, 02:25 PM

I'll have to create a new workbook to post. In reading your comment, maybe VLOOKUP isn't what I want to be using?

Sheet 2 has 5 columns. Column A represents letter/number combinations such as A1-A532. Columns B, C, and D are populated with TRUE or FALSE. Columns BCD are TRUE if they meet the requirement to have a Column A
value assigned to it (this was done manually, not by a function), and FALSE if they don't meet the requirement.

Column E is another TRUE/FALSE column based on a Yes/No scenario.

What I am wanting to do is this by having a drop down menu on a different sheet:

IF Column E is True AND Column B OR Column C OR Column D is True

Print the information in the corresponding row from Column A

Each row is a unique letter/number combo. Each Column is the exact same length which corresponds to Column A.

Column E MUST be TRUE and either Column from B, C or D MUST be TRUE to return the info in Column A. I just want to be able to use a drop down to choose which Column (B,C or D) that I want to validate against.

I'll try to come up with a quick workbook...but hopefully this somewhat explains it.

Should mention that I will only be validating Column E against either B, C or D based on the selection made from the drop down box if that wasn't clear.

Thanks for help!

**Shanebass** · 11-24-2017, 01:16 PM

I figured it out. In the final product Sheets 1 and 3 will be hidden. What I was going for is picking a control set based on the variables in sheet 1 Column(s)B, C or D being TRUE and Column E being TRUE. Sheet 2 is where users will choose the variable. Sheet 3 is where the logic resides. Column A determines which variable is TRUE and if Column E is TRUE. If column E is not TRUE, the default is "Not Selected" regardless of the variable chosen. Column B is the logic behind which variable was chosen. This is the part I was getting hung up on previously. I just made the choice either TRUE or FALSE and then wrote and IF/AND statement based on the choice.

It works. Might not be the prettiest but it accomplishes what I want. I wouldn't mind having a few different independent reviews to make sure to make sure I am right though.

Thanks!