I have an array, looking something like this (but with other numbers)
0.1 0.2 0.3 0.4.....
1 1 5
2 1 5
3 1 5
4 1 5
..
..
..

My challenge is, that I want to enter a number from column A and another number from the array and then have returned the corresponding value from row 1. If I enter 1 and 5 it should retuirn 0.4 and 3 and 1 should return 0.2.

All functions that I know I returns something from the array, not based on it.

I have tried with this

=OFFSET(INDIRECT(ADDRESS(MATCH(Udregninger!B30,A2: A80,0)+1,MATCH(Udregninger!B31,INDIRECT("a"&MATCH( Udregninger!B30,A2:A80,0)&":bl"&MATCH(Udregninger! B30,A2:A80,0)),0)-1)),-MATCH(Udregninger!B30,A2:A80,0)+1,0)

But it only works on some combinations.

Udregninger!B30 and Udregninger!B31 is where I enter the numbers to look for.

Any good ideas?

Jan

Hi Jan,

Am Mon, 20 Oct 2014 03:48:54 -0700 (PDT) schrieb Jan Kronsell:

I have an array, looking something like this (but with other numbers)
0.1 0.2 0.3 0.4.....
1 1 5
2 1 5
3 1 5
4 1 5
.
try:
=INDEX(1:1,MATCH(Udregninger!B31,INDIRECT(MATCH(Ud regninger!B30,A1:A80,0)&":"&MATCH(Udregninger!B30, A1:A80,0)),0))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus

It did the job, thank you. My own formula was a bit more complicated, and did not work like it was supposed to :-)

=OFFSET(INDIRECT(ADDRESS(MATCH(Udregninger!B30,A1: A79,0)+1,MATCH(Udregninger!B31,INDIRECT("a"&MATCH( Udregninger!B30,A1:A79,0)&":bl"&MATCH(Udregninger! B30,A1:A79,0)),0)-1)),-MATCH(Udregninger!B30,A1:A79,0)+1,0)

Regards
Jan

Den mandag den 20. oktober 2014 13.29.47 UTC+2 skrev Claus Busch:
Hi Jan,



Am Mon, 20 Oct 2014 03:48:54 -0700 (PDT) schrieb Jan Kronsell:



I have an array, looking something like this (but with other numbers)

0.1 0.2 0.3 0.4.....

1 1 5

2 1 5

3 1 5

4 1 5

.

try:

=INDEX(1:1,MATCH(Udregninger!B31,INDIRECT(MATCH(Ud regninger!B30,A1:A80,0)&":"&MATCH(Udregninger!B30, A1:A80,0)),0))





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional