complex lookup

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

I have a lookup table that I need to reference. based on a 3 digit # such as
"2.22", I need to be able to find the cross section on the lookup table.
Based on numbers across the top and down the left side;
If I have the number 2.22, it will return for me .15 from the table

lookup table (made up values)
.00 .01 .02 .03 .04 ... .09
2.0 .31 .32 .33 .34 .35 ... .50
2.1 .21 .35 .51 .51 .25 ... .85
2.2 .53 .52 .15 .52 .51 ... .81
2.3 .89 .58 .23 .45 .15 ... .15
 
Doug said:
I have a lookup table that I need to reference. based on a 3 digit # such as
"2.22", I need to be able to find the cross section on the lookup table.
Based on numbers across the top and down the left side;
If I have the number 2.22, it will return for me .15 from the table

lookup table (made up values)
.00 .01 .02 .03 .04 ... .09
2.0 .31 .32 .33 .34 .35 ... .50
2.1 .21 .35 .51 .51 .25 ... .85
2.2 .53 .52 .15 .52 .51 ... .81
2.3 .89 .58 .23 .45 .15 ... .15
Click to expand...


http://www.contextures.com/xlFunctions03.html#IndexMatch2

Assuming your table above is in A1:K5, and 2.22 is in A10, try this:

=INDEX($B$2:$K$5,MATCH(ROUND(A10,1),$A$2:$A$5,0),
MATCH(A10-ROUND(A10,1),$B$1:$K$1,0))
 
I am recieving a #N/A in the field. This is what it looks like now that I
modified it to fit what I am doing. Can you see what may be wrong? $E$8 is
the lookup value and the table is in $P$2:$Z$41.

=INDEX($Q$3:$Z$41,MATCH(ROUND($E$8,1),$P$3:$P$41,0),MATCH($E$8-ROUND($E$8,1),$Q$2:$Z$2,0))
 
Doug said:
I am recieving a #N/A in the field. This is what it looks like now that I
modified it to fit what I am doing. Can you see what may be wrong? $E$8 is
the lookup value and the table is in $P$2:$Z$41.

=INDEX($Q$3:$Z$41,MATCH(ROUND($E$8,1),$P$3:$P$41,0),MATCH($E$8-ROUND($E$8,1),$Q$2:$Z$2,0))
Click to expand...


If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

Make sure that what looks like a number is actually a number (and not text) in
cell E8, column P and row 2.

One way to convert them would be to copy a blank cell, select the values in
question, then Edit / Paste Special / Values / Add / OK. This will not change
cells that are already numbers.
 
Not sure why it is not working. Everything you said makes sense, I went back
and checked all the number formats, etc. I will just have to play around and
maybe look up the details to the functions you gave me. You have helped a
great deal.
 
Not sure why it is not working. Everything you said makes sense, I went back
and checked all the number formats, etc. I will just have to play around and
maybe look up the details to the functions you gave me.  You have helped a
great deal.
--
Thank you!








- Show quoted text -
Click to expand...

This isnt going to help you much but the formula isnt working because
despite appearances, the MATCH function doesnt think $E$8-ROUND($E
$8,1) matches the value in your table.

I cant think why not. If you seperate the formula out and do A1=B1
(being the round function alone and the number in your grid) excel
returns TRUE, so the values are the same. It must be something about
the MATCH function

regards


David
 
Doug said:
Not sure why it is not working. Everything you said makes sense, I went back
and checked all the number formats, etc. I will just have to play around and
maybe look up the details to the functions you gave me. You have helped a
great deal.
Click to expand...


Did you just check the number formats, or did you actually confirm that there
are numbers in the cells, following the directions I gave previously?

Also, make sure that the numbers exactly match what you expect them to be, for
example make sure it is .02 in the cell and not .020000001 formatted to show
only 2 decimal places.
 
Did you just check the number formats, or did you actually confirm that there
are numbers in the cells, following the directions I gave previously?

Also, make sure that the numbers exactly match what you expect them to be, for
example make sure it is .02 in the cell and not .020000001 formatted to show
only 2 decimal places.
Click to expand...

Glenn / Doug

The only way I managed to get this to work, was to make sure
'Precision as displayed' was selected in the OPTIONS.

the ROUND function is causing the MATCH function to fail, this seems
to resolve the problem

Regards

David
 
Hello Doug,

Array-enter
=INDEX(Q3:Z41,MATCH(TEXT(FLOOR(E8,0.1),"0.0"),TEXT(P3:P41,"0.0"),
0),MATCH(TEXT(MOD(E8,0.1),".00"),TEXT(Q2:Z2,".00"),0))

Regards,
Bernd
 
Back
Top