Super Dragon 64 wrote:Balladeer wrote:Hm, can you give a dummy example? E.g. a fake table with fake labels, what inputs you're putting in and what output you'd want (from the fake table)? My solution would usually be to use index-match at anything like this but I want to make sure it'd work.
I have table like the below where each column has a five letter word before the country name and each row is a month/year. I'm trying to create a formula that is in another table and it reads the month/year, country and five letter word which in their own columns. It then looks up the corresponding value in the table below.
So if my reference table had Jul-23, Orange, UK in its columns, the formula would return 745.
You may be able to use an index and match approach, like below.
Here's the formula:
=INDEX($C$6:$G$9,MATCH(J6,$B$6:$B$9,0),MATCH(CONCATENATE(K6," ",L6),$C$5:$G$5,0))
It indexes the table data ($C$6:$G$9) by the row where $B$6:$B$9 matches the date in J6 and the column where the $C$5:$G$5 matches the concatenated text in K6 and L6 with a space in between.
Edit: Match isn't case sensitive so it won't work if your 5-letter codes may differ only by case.