|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 22nd, 2010, 09:51 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using match but data needs to be split.
I have a table - Y axis contains whole numbers (1 - 20) and X axis contains
decimals (0.1 - 0.9). For each there is a corresponding reference that I want to be able to look up. For example, if I type 10.5, I want to be able to look up the value held at 10 on the X axis and 0.5 on the Y axis. I've tried using the index and match functions, but it doesn't work. I then looked at using RIGHT and LEFT functions to break the components down which also did not work. If anyone can help it would be much appreciated. Thanks. 
|
|
#2
March 22nd, 2010, 09:59 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using match but data needs to be split.
Hi,
One way =INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0)) Whare A1:E20 is the entire table. F1 is the column lookup vaue G1 is the row lookup value The formula returns the intersect -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "gyzmo" wrote: I have a table - Y axis contains whole numbers (1 - 20) and X axis contains decimals (0.1 - 0.9). For each there is a corresponding reference that I want to be able to look up. For example, if I type 10.5, I want to be able to look up the value held at 10 on the X axis and 0.5 on the Y axis. I've tried using the index and match functions, but it doesn't work. I then looked at using RIGHT and LEFT functions to break the components down which also did not work. If anyone can help it would be much appreciated. Thanks.
|
|
#3
March 22nd, 2010, 10:14 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using match but data needs to be split.
Hi,
I missed the bit about the lookup value being in one cell and needing splitting so try this =INDEX(A1:E20, MATCH(INT(F1),A1:A20,0), MATCH(ROUND(MOD(F1,1),2),A1:E1,0)) Lookup value in F1 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, One way =INDEX(A1:E20, MATCH(F1,A1:A20,0), MATCH(G1,A1:E1,0)) Whare A1:E20 is the entire table. F1 is the column lookup vaue G1 is the row lookup value The formula returns the intersect -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "gyzmo" wrote: I have a table - Y axis contains whole numbers (1 - 20) and X axis contains decimals (0.1 - 0.9). For each there is a corresponding reference that I want to be able to look up. For example, if I type 10.5, I want to be able to look up the value held at 10 on the X axis and 0.5 on the Y axis. I've tried using the index and match functions, but it doesn't work. I then looked at using RIGHT and LEFT functions to break the components down which also did not work. If anyone can help it would be much appreciated. Thanks.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
