Excel lookup / array

Question

I would be grateful for your help on the below.

I have two tables as per the image below, one on the left and one on the right.

Each row in the left table has "Genre" & "Age Years". Under the years 2020 to 2024 I want to pull in the % from the right had side table. There are two values it has to be equal to, the correct genre and be within the correct Age Years range.

Therefore if it was TV and Age Years was 3 it would return 10% but if it was TV and 4 years old it would return 3%

I have tried Vlookup and Index Match with little success.

It's always good practice to show your efforts, please include the formulas you have tried. — EEM
– EEM, Commented Jan 18, 2020 at 18:06

basic · Accepted Answer · 2020-01-19 00:33:35Z

1

If I understand correctly and in columns from 2020 to 2024 should have the same value for each genre and age, then it is enough to combine the HLOOKUP and MATCH functions in the array formula:

{=HLOOKUP($B2;$M$1:$Y$6;MATCH(1;--($A2=$L$2:$L$6);0)+1;TRUE)}

Array formula after editing is confirmed by pressing ctrl + shift + enter

answered Jan 19, 2020 at 0:33

basic

12k2 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel lookup / array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related