0

I have this data set.

enter image description here

At A9 and B9, I have used the "List" option under the "Data validation" tab to get the dropdown option. The result at C9 should be populated from the above matrix.

enter image description here

In this example, C9 should have "NA"

Regards - Anil

Improve this question
1
  • What formula are you using?
    – Reddy Lutonadio
    Commented Dec 14, 2022 at 12:55

2 Answers 2

Reset to default
3

Are you trying to build a formula

=OFFSET($A$1;MATCH(A9;$A$2:$A$5;0);MATCH(B9;$B$1:$E$1;0))

In general terms, this is written as

=OFFSET(<start_of_table>;N(MATCH(<row_header>;<first_column_of_table>;0));N(MATCH(<column_header>;<first_row_of_table>;0)))

Improve this answer
3
  • I think he wants the look up value from ABC and Europe (which have dropdowns)
    – WeAreOne
    Commented Dec 14, 2022 at 14:24
  • @Prema Yes, that's right, exactly as described in the question. Are you confused by using OFFSET() instead of INDEX()? Don't worry, both formulas, both yours and mine, give the same result.
    – JohnSUN
    Commented Dec 14, 2022 at 14:42
  • you are right! Different, but interesting approach!
    – WeAreOne
    Commented Dec 14, 2022 at 14:58
2 
=INDEX(A1:E5;MATCH(A9;A1:A5;0);MATCH(B9;A1:E1;0))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .