Choose and Match

[Atif]

Hi All,

Site TV MP3 Cell Phone

NY Margin ($) $338,101 $292,277 $377,098
Margin (%) 34% 29% 41%

NJ Margin ($) $282,301 $261,081 $243,448
Margin (%) 28% 26% 26%

i have above data, i want for example if user choose NY and TV from their
drop down list $ 338,101 and 34% appear in respective cells

any suggestion

 

[Sean Timmons]

that would be INDEX and MATCH

=INDEX(B2:X500,MATCH("NY", B2:B500,0),MATCH("TV",B2:X2,0)) would return the
margin $ amount.

=INDEX(B2:X500,MATCH("NY", B1:B499,0),MATCH("TV",B2:X2,0)) would return the
margin %.

This assumes the table is in B2:X500.
I entered "NY" and "TV", but you'd, of course, change to the cell that will
have the entered values...

Please adjust values in index and both match statements to fix issue.

 

[Atif]

THANKS!

that would be INDEX and MATCH

=INDEX(B2:X500,MATCH("NY", B2:B500,0),MATCH("TV",B2:X2,0)) would return the
margin $ amount.

=INDEX(B2:X500,MATCH("NY", B1:B499,0),MATCH("TV",B2:X2,0)) would return the
margin %.

This assumes the table is in B2:X500.
I entered "NY" and "TV", but you'd, of course, change to the cell that will
have the entered values...

Please adjust values in index and both match statements to fix issue.

 

[ryguy7272]

This is a great resource for Index/Match
http://www.contextures.com/xlFunctions03.html


Regards,
Ryan---