Trouble with lookups and name ranges

Hello,

I am trying to find the right formula to return data required in sheet. I have attached a file which contains my data on the left and the row where I want to perform the calculation on the right.

In the calculation row, I basically want a formula in U3 that will say: lookup the contents of columns L, M, N in the table and return the right answer (in this example that would be Cell b:24).

I have tried lookup tables and named ranges but obviously doing something right. I need a formula that would work with lots of variables in columns L,M,N.

Many thanksCLASS SAMPLE.xlsx

in your data, given the three parameters there are 2 possible answers how do you isolate the 24th row?

Sorry, uploaded the wrong version of the sample file. Yes you are both right, MDN appeared twice which I have corrected in the new uopload file

NEW CLASS SAMPLE.xlsx

Thank you for the reply. I fear that this will not work, as I said in my original post, there will be lots of variables in columns L,M,N. Of these, there will be times when L=MDN, M=331 and N will = something else other than ALB. Therefore, each of the 3 columns (L,M,N) will have variables, and there will be times when 2 of them will be the same as other variations.

Basically, column L is a class (there are about 8 of these classes), column M is a venue and column N is a distance. There are multiple distances at any venue, there are multiple times different venues have the same distance - and all combinations of those 2 variables all have a variety of all 8 of the classes.

So I need something that will do a match on all 3 variables if that is possible.

Just further to the above post (reply). I can get a similar index/match formula to work when there are only 2 variables, but I just cant get the syntax right to make it work with all 3 variables.

I think that a search key buildup is in place. combine ALB&331 and all likes in a row so you have only 2 variables without losing the uniqueness of the third variable

Originally Posted by JBeaucaire

Your data does not demonstrate how other values other than ALB will be represented in the main table. Update your sample. Show a complete data set with 2-3 different examples of the LMN lookup values and the expected results.

I have attached a new sample file to better show variables.NEW CLASS SAMPLE.xlsx

Thank you so much, true genius at work.