# Selective Input using VLOOKUP against two cell's data.

#### Lochlainn

##### New Member
Ok guys and gals. My brain is fried. Firstly, let me say I do not know VBA. I know most formulas. I have a rather intricate spreedsheet and I have 99.9% of the formulas finished. So here is my finial delima for today:

(E13) is the manual input Year in (YYYY) format

(E14) Has a dropdown of (Blank), Cumulative or Sequential

(E15) Has a dropdown of (Blank), First Quarter, Second Quarter, Third Quarter or Fourth Quarter

(E16) Needs to use a formula to look at (E14), determine if it is (Blank), Cumulative or Sequential, then look at (E15) to see what 'Quarter' it is. If (E14) says 'Cumulative' AND (E15) has a value other than (Blank), then it should enter 'January 1'. If (E14) is (Blank), then it should remain (Blank) no matter what (E15) has. If (E14) says 'Sequential', then it should should "VLOOKUP" column 2 of the same table that (E17) "VLOOKUP"s.

(E17) Uses this formula to enter the proper 'Ending Date' for the report:

=IF(ISNA(VLOOKUP((E15),E58:G62,3,0))," ",IF(VLOOKUP((E15),E58:G62,3,0)=0," ",(VLOOKUP((E15),E58:G62,3,0)&","&" "&(E13))))

Which comes out 'Month Day, Year'.

(E17) uses the Quarter dropdown from (E15) to determine the VLOOKUP range. If (E15) is blank, it leaves the cell blank. Otherwise is fills accordingly.

Currently, I am trying this formula:

=IF((E14)="Cumulative"&(ISTEXT(E15)),(F59)&","&" "&(E13),IF(ISNA(VLOOKUP((E15),E58:G62,2,0))," ",IF(VLOOKUP((E15),E58:G62,2,0)=0," ",(VLOOKUP((E15),E58:G62,2,0)&","&" "&(E13)))))

But, it's bypassing to the VLOOKUP and changing the 'Begining Date' to the start of each Quarter, even if (E14) says 'Cumulative'. So I tried this formula:

=IF((E14)="Cumulative",(F59)&","&" "&(E13),IF(ISNA(VLOOKUP((E15),E58:G62,2,0))," ",IF(VLOOKUP((E15),E58:G62,2,0)=0," ",(VLOOKUP((E15),E58:G62,2,0)&","&" "&(E13)))))

But that inputs the 'January 1' whenever (E14) says 'Cumulative' and does not wait for (E15) to have an input.

So what I would like for the formula to do is:

IF (E14)='Cumulative' AND there ISTEXT in (E15), then input 'January 1', else go to the VLOOKUP argument. Anyone think they can figure this out? I'm obviously very close, I just don't know how to make it check both cells before inputing the 'January 1' data.

Thanks a ton,
Loch

Last edited:

#### jarjarbingie

##### Well-known Member
=IF(AND(E14="Cumulative", E15<>""), "January 1", VLOOKUP formula)

#### Lochlainn

##### New Member
Thank you jarjarbingie! Worked like a charm. I think I can finally put this report to bed. Now on to the next report and ledger combo. You guys are gonna love it. Makes this question look like child's play.

1,082,176
Messages
5,363,577
Members
400,751
Latest member
wheddingsjr

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...