That,sir, is perfect. Although I am finding it easier to re-write the
formula than to use that blasted wizard.
But what if I need it to lookup a number range instead of an exact
number? Say, instead of looking up 36, it looks for any number between
30-40. Is it just a matter of changing MATCH to BETWEEN or whatever the
command would be?
Thanks again!
John James Wrote:
Hi Sharkfoot
The formula for your worksheet is:
=INDEX(Residuals!$A$1:$K$5, MATCH(D6,Residuals!$A$1:$A$5,),
MATCH(D7,Residuals!$A$1:$K$1,))
This will not work in your particular spreadsheet until you convert
your relevant headings and lookup values to text. You can convert the
cells containing the headings (i.e. the first column and first row in
your table) to text by highlighting them and changing the cell format
to text. You can similarly convert the cells containing your lookup
values to text through the same method. You'll probably need to force
recalculation of some of these cells so they are recognised as text
instead of values. Pressing the F2 key and pressing enter after making
the above changes will work. It's probably only the lookup value cells
that you will need to do this for.
For future reference, if you want help recreating this rather ugly but
very useful index & match formula, then install the Lookup Wizard
add-in.
--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile:
http://www.excelforum.com/member.php...o&userid=32164
View this thread:
http://www.excelforum.com/showthread...hreadid=526951