Using match but data needs to be split.

[gyzmo]

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.

 

[Mike H]

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.

 

[Mike H]

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.