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.

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.

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.