If function and vlookup with merged cells?

Hi Guys,

I'm looking to pull data from Sheet-2 onto Sheet-1 Summary table (see attached).

Issue is that I have merged cells/headers on row-1 (e.g. US, UK, Japan). I understand that creating drop-down list using merged cells is probably not ideal, so open to other suggestions for creating a drop-down without blanks.

Basically what I want to see on the summary table (sheet-1), is that whatever value I pick on the drop down list, the corresponding monthly and yearly costs should populate from sheet-2.


Any insight on what logic and functions i could apply would be greatly appreciated.

Cheers!
Varun

Put this on C5, and ENTERED as ARRAY FORMULA, then copied down and cross:

=IFERROR(INDEX(Prices!B$3:$G$6,SMALL(IF((INDEX(Prices!$B$3:$G$6,0,MATCH($C$3,LOOKUP(COLUMN(Prices!$B$1:$G$1),COLUMN(Prices!$B$1:$G$1)/(Prices!$B$1:$G$1<>""),Prices!$B$1:$G$1),0))<>""),MATCH(ROW(Prices!$B$3:$B$6),ROW(Prices!$B$3:$B$6)),""),ROW(A1)),MATCH($C$3,LOOKUP(COLUMN(Prices!$B$1:$G$1),COLUMN(Prices!$B$1:$G$1)/(Prices!$B$1:$G$1<>""),Prices!$B$1:$G$1),0)),"-")

Another way:

=VLOOKUP($B5,Prices!$A$3:$G$6,MATCH($C$3,Prices!$A$1:$G$1,0)+(C$4="Yr"),0)

thank you! this worked on my sheet and much more easy to understand and apply. Just one quick question - what purpose does the +(C$4-"Yr") serve in the formula?

Thanks a lot!

(C$4="Yr") -> result is true or false

+false -> +0

+true -> +1

like this

+if(C$4="Yr",1,0)

sorry Phuocam, still couldn't understand.

Would you mind elaborating on this piece of logic plz: MATCH($C$3,Prices!$A$1:$G$1,0)+(C$4="Yr"),0)

Thank you!!

MATCH($C$3,Prices!$A$1:$G$1,0) result is 2,4,6

+(C$4="Yr"):

C$4= Month then result is 2,4,6

D$4 = Yr then result is 3,5,7

Thanks AliGW! appreciate the clarification

awesome! this is making some sense now

Quick ques Phuocam:

Could you explain how does MATCH($C$3,Prices!$A$1:$G$1,0) gives results of 2,4,6?

Sorry for asking too many questions, but you have been really great!