Vlookup Question

[Cameron419]

Hello, can I have more than one lookup value in a vlookup to return a value in another cell when a selection is made in a dropdown list?

The example I have is:

Dropdown list Columns:
C17
C18
C19
C20

If the user selects from the dropdown list a region that starts with a L in front of it, I need a note to pop up in C21. I can only get this note to pop up if the selection of a region that starts with a L is made in C17 but if it is not made in C17 and it is selected in C18 or C19 or C20, the note will not appear in C20.

Here is my vlookup currently:
=IF(ISNA(VLOOKUP(C17,'Periods & COGS'!V2:W41,2,FALSE)),"",(VLOOKUP(C17,'Periods & COGS'!V2:W41,2,FALSE)))

This is the result note when the selection is made at C17:
[TABLE="width: 332"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]SELECT UP TO 4 SERVICING PLANTS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]C17 - PLANT 1[/TD]
[TD]L - Detroit[/TD]
[/TR]
[TR]
[TD]C18 - PLANT 2[/TD]
[TD]No Selection [/TD]
[/TR]
[TR]
[TD]C19 - PLANT 3[/TD]
[TD]No Selection[/TD]
[/TR]
[TR]
[TD]C20 - PLANT 4[/TD]
[TD]No Selection[/TD]
[/TR]
[TR]
[TD="colspan: 2"]C21 -*Note: Advanced Notice of 4 Weeks for Pricing is Required Before First Order is Submitted

Thanks[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

would =if(or(left(c17,1)="L",left(c18,1)="L",left(c19,1)="L",left(c20,1)="L"),"note","") or similar work for you ?

 

[Cameron419]

Hello, I'll give it a try this morning. I tried an Index formula and it didn't work so I'm hopeful...

I'll let you and thank you, I appreciate your response.

 

[Cameron419]

OMG, this totally worked!!!! You are an Excel Genius and I applaud you!!!! Thank you sooo much!!!!!

 

[steve the fish]

Another couple of options:

=IF(SUMPRODUCT(0+(LEFT(C17:C20,1)="L")),"Note","")
=CHOOSE(SUMPRODUCT(0+(LEFT(C17:C20,1)="L"))+1,"No L","One L","Two L","Three L","Four L")

 

[Cameron419]

Thank you Steve the Fish, those additional options work also!!! This board is awesome!!!

 

[oldbrewer]

we try to please - steve the fish is way out of my league though...........

 

[Cameron419]

The Formulas you all provided have helped me with a project in a lot of areas of the workbook I'm automating. I hope to convey that this request that was solved was awesome!!!! Thank you again!!!