Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with looking up a value.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with looking up a value.
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with looking up a value.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel startup problem. Possible problem with stdole32.tlb | Excel Programming | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |