question about lookup function displaying value from table not only list

Hi all,

I am new here, so hello to everybody, have looked a little around and this seems to be a very interesting platform.

I have found the forum because of an actual problem or question I have and I hope that some of you have and idea how to solve this.

I know how to make a lookup function to display content from different lists etc but in this case I want to lookup a value which is the result of a specific row and column, basically like a coordinate. I made a simple example attached:


1. When selecting a value from the first drop down box it shows the selection in linked cell 1
2. When selecting a value from the second drop down box it shows the selection in linked cell 2 (here I actually dont understand why a list fill range is not working for a horizontal range?)

3. Based on the selection in the linked cells I want a function in cell F24 which shows the result based on the selected row and column
such as if 12 is selected from first box and 150 is selected from second box then F24 should give 6,7.


Unfortunately I have so far not managed to do this a lookup function as I normally use it will only lookup vertical lists.

Looking forward to hear from you and thanks in advance for helping. I hope I will also be able to contribute with something sometimes.

Regards,
Tom

Hi
try an INDEX/MATCH combination. It's well documented all around.

Hi All,

a sumproduct could do the trick too:

=SUMPRODUCT(B4:J14*(A4:A14=M6+0)*(B3:J3=S6+0))

Regards

Thank you very much Canapone for this simple solution

I tried to figure out how to solve this with INDEX/MATCH but your solution is great and much easier.

Regards,
Tom

Hi All,

thanks for you kind feedback.

As suggested by Pecoflyer, INDEX/MATCH is a good combination to get the number

In the file you shared, M6 and S6 are not read as numbers.

If you would like to try INDEX/MATCH

=INDEX(B4:J14,MATCH(M6+0;A4:A14,0),MATCH(S6+0,B3:J3,0))

you could exploit the ascending order of the numbers in the two axes

=INDEX(B4:J14,MATCH(M6+0;A4:A14),MATCH(S6+0,B3:J3))

or try a formula like the following

=INDEX(B4:J14,RANK(M6+0,A4:A14,1),RANK(S6+0,B3:J3,1))


Just for fun: there are other ways using MAX or array SUM or OFFSET...

Thanks again for detailed answer and explanations, was really nice to see also other ways of doing this.

Regards,
Tom