Lookup tables with multiple columns

[sharkfoot]

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!

 

[Guest]

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)

 

[sharkfoot]

I have attached a very small version of the workbook I am trying to
manipulate.

There are two fields in the data entry sheet that the user will
enter(highlighted in yellow). Based on the results of those 2 cells, I
need to get a value returned in E15 of the Data Entry sheet, which it
pulls from the sheet named "Residuals". In this case, it would be
Residuals!F4 that was returned in Data Entry!E15, as you can see by
looking at the residuals sheet.

It seems a bit more complex than a simple lookup table. How do I make
this a reality?

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)

+-------------------------------------------------------------------+
|Filename: please_help.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4534 |
+-------------------------------------------------------------------+

 

[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 conver
your relevant headings and lookup values to text. You can convert th
cells containing the headings (i.e. the first column and first row i
your table) to text by highlighting them and changing the cell forma
to text. You can similarly convert the cells containing your looku
values to text through the same method. You'll probably need to forc
recalculation of some of these cells so they are recognised as tex
instead of values. Pressing the F2 key and pressing enter after makin
the above changes will work. It's probably only the lookup value cell
that you will need to do this for.

For future reference, if you want help recreating this rather ugly bu
very useful index & match formula, then install the Lookup Wizar
add-in

 

[sharkfoot]

That,sir, is perfect. Although I am finding it easier to re-write th
formula than to use that blasted wizard.

But what if I need it to lookup a number range instead of an exac
number? Say, instead of looking up 36, it looks for any number betwee
30-40. Is it just a matter of changing MATCH to BETWEEN or whatever th
command would be?

Thanks again!

 

[sharkfoot]

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!