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!


--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=526951

Lookup tables with multiple columns
 

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

Lookup tables with multiple columns
 

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?

Kevin B Wrote:
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


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

--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=526951

Lookup tables with multiple columns
 

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.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=526951

Lookup tables with multiple columns
 

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

Lookup tables with multiple columns
 

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!


--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=526951