Unable to Combine IF and Vlookup function with multiple Variables

[Oscar Mike]

Good Day!


I am having a problem combining the If and Vlookup function in one of the Worksheet I am working on.

The idea is to create a formula that behaves according to the value of two reference cells.

In my Case, based on the attached Excel File, cells B3 and C3 are both drop-down menus with unique 'names'.

Now, I need a formula on another cell (D3) that would draw data from a list (=I3:L30) based on the combination of values ('names') from cells B3 and C3. The Vlookup formula would also place a text on the next cell, E3, as per the function.

I have been successful in the past when there was only one drop-down menu, but now that I have to take into consideration two changing variables, i'm at a lost on how to make it work.

I tried stringing multiple IF functions with embedded VLOOKUP formulas but I keep on getting the 'too many functions error' on the cell.

For a greater appreciation of my dilemma, this hope to function as tip sheet to help my end-users get specific phone numbers of specific programs per specific state. We only have limited space for this module so we can also spare a few cells on this on the specific worksheet

1. B3 - THEY WILL CHOOSE THE PROGRAM #
2. C3 - THEY WILL ALSO CHOOSE THE STATE #
3. D3 - THE FORMULA WILL GIVE OUT THE NUMBER FOR PROGRAM X and STATE X
4. E3 - ADD ANY NOTE FROM THE LIST IF AVAILABLE'

Thank you very much for taking the time to read my post and any help would be greatly appreciated.


Thank you!

 

[Nebu]

Hi:

Is this what you are looking for?

Thanks

 

[bosco_yip]

Another option without helper.

In D3, formula copy right to E3

=INDEX(K$3:K$30,MATCH($B$3,$I$3:$I$30,0)+MATCH($C$3,$M$10:$M$15,0))

Regards
Bosco

 

[Haseeb A]

Hello Mike,

Here is another way, even states in column J are not in order & always available under a group.

=VLOOKUP($C3,INDEX($J:$J,MATCH($B3,$I:$I,0)):INDEX($L:$L,2^20),2,0)

Change red highlighted column # CODE#

Or if states are not always available in a group,

=INDEX(K3:K30,MATCH(1,INDEX((J3:J30=C3)*(LOOKUP(ROW(J3:J30),ROW(J3:J30)/(I3:I30<>""),I3:I30)=B3),),))

 

[Oscar Mike]

Good Day Guys!

Thank you very much for your replies.

I'll experiment on all your suggestions and give you updates as soon as possible.

Again, Thank you very much for taking the time to answer my inquiry; I have heard of the scale of possibilities in excel and I'm just learning to appreciate how we can possibly arrive at the same solution to a problem using a variety of ways.

regards,
arnel