Lookup tables with multiple columns

I have a table that stretches from A1:X45 and every cell has a different interest rate in it. Do I use lookup tables to identify the rates from my data entry sheet? I need to find the appropriate value both horizontally and vertically so I don't know how to approach this? Thanks!

The third argument in the VLOOKUP is the offset column, so if you have a way
you can determine which column to extract the from when it finds a matching
value it doesn't matter how many columns in your lookup table, excluding the
actual limit of 256.

VLOOKUP(Lookup_Value, Lookup_Table_Location,Return_Value_Column)


--
Kevin Backmann


"sharkfoot" wrote:

>
> I have a table that stretches from A1:X45 and every cell has a different
> interest rate in it. Do I use lookup tables to identify the rates from
> my data entry sheet? I need to find the appropriate value both
> horizontally and vertically so I don't know how to approach this?
> Thanks!
>
>
> --
> sharkfoot
> ------------------------------------------------------------------------
> sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
> View this thread: http://www.excelforum.com/showthread...hreadid=526951
>
>

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.

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!

Originally Posted by John James

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.

OK, can someone try to field this one for me? I need to be able to search for a range instead of an exact match. Who knows about this aspect? Thanks again!