0

I have a table with variables in two columns. I want to find a value from a third column that matches the criteria in both first columns:

table

The search criteria will be defined dynamically (i.e., the city and direction will change based on another result). I want to do this with a formula and no macro (but that should be possible, I hope ;) ). I tried various INDEX and MATCH formulas, but none gave me the correct result.

sportster
  • 3
  • 1

1 Answers1

0

XLOOKUP() would be best in this case. Try-

=XLOOKUP(1,(A1:A8=F1)*(B1:B8=G1),C1:C8)

You can also use FILTER() function.

=FILTER(C1:C8,(A1:A8=F1)*(B1:B8=G1))

In case of older version of excels you may try INDEX/MATCH with array entry by CTRL+SHIFT+ENTER

=INDEX(C1:C8,MATCH(1,(A1:A8=F1)*(B1:B8=G1)))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36