vlookup from variable matrix

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use the vlookup function (probably) to draw a value from a matrix based on a variable from a spreadsheet which would direct which column from the matrix to draw the end result. I.e. the spreadsheet would list a date and the vlookup function would go to the matrix and find the date in the left column, from there the spreadsheet would specify another variable which would be a time range (1 year, 2 years, 3 years, etc), those time ranges would be the matrix columnar headings. Based upon the time range variable noted in the spreadsheet, the applicable value would be selected from the matrix based upon the date in the left column and the period in the top row. See below for the sample matrix:

1 year 2year 3year 4year
Jan 1.1 2.2 3.3 4.4
Feb 1.5 1.9 2.1 3.1
Mar 1.8 2.2 2.8 3.0
Apr 1.6 1.9 2.0 2.5
May 1.5 1.8 2.2 2.4
 
Jay,

If I understand your question, you can use the Double Lookup
method described at www.cpearson.com/excel/lookups.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Jay M said:
I am trying to use the vlookup function (probably) to draw a
Click to expand...
value from a matrix based on a variable from a spreadsheet which
would direct which column from the matrix to draw the end result.
I.e. the spreadsheet would list a date and the vlookup function
would go to the matrix and find the date in the left column, from
there the spreadsheet would specify another variable which would
be a time range (1 year, 2 years, 3 years, etc), those time
ranges would be the matrix columnar headings. Based upon the
time range variable noted in the spreadsheet, the applicable
value would be selected from the matrix based upon the date in
the left column and the period in the top row. See below for the
sample matrix:
 
Hi

=OFFSET(Sheet1!$A$1,MATCH(Cell1,Sheet1!$A$2:$A$13,0),MATCH(Cell2,Sheet1!$B$1
:$E$1,0))
where Cell1 is reference to cell with month and Cell2 is reference to cell
with year
or
=INDEX(Sheet1!$B$2:$E$13,MATCH(Cell1,Sheet1!$A$2:$A$13,0),MATCH(Cell2,Sheet1
!$B$1:$E$1,0))

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Jay M said:
I am trying to use the vlookup function (probably) to draw a value from a
Click to expand...
matrix based on a variable from a spreadsheet which would direct which
column from the matrix to draw the end result. I.e. the spreadsheet would
list a date and the vlookup function would go to the matrix and find the
date in the left column, from there the spreadsheet would specify another
variable which would be a time range (1 year, 2 years, 3 years, etc), those
time ranges would be the matrix columnar headings. Based upon the time
range variable noted in the spreadsheet, the applicable value would be
selected from the matrix based upon the date in the left column and the
period in the top row. See below for the sample matrix:
 
Chip,
Yeow, that was quick. Thanks for the prompt response. I'll go there and check it out.
Jay McBee
 
Chip,
I went to the website and found the double lookup and also the additional lookup options.
Will it work to do a closest match double lookup. Re: the closest match example towards the bottom of your lookup examples. This would entail rather than the column header being fixed amounts (i.e. year 1, year 2, year 3 and the rows being the months Jan - Dec) that the cell reference would be a period of 21 months or 38 months or whatever, where it would have to test it to see which column to draw from and same for the month in the row, i.e. pick what month a date falls in, such as Feb 14, 2003. I tried combining these but ended up with #VALUE! error.
 
Jay, using you example from the original post, assume the data starts in B1
with 1 Year and
Jan in A2. With the number of lookup months for the time range in G2 and the
date lookup in H2


A B C D
E F G H
1 year 2 year 3 year 4
year
Jan 1.1 2.2 3.3
4.4 20 02/14/03
Feb 1.5 1.9 2.1
3.1
Mar 1.8 2.2 2.8
3.0
Apr 1.6 1.9 2.0
2.5
May 1.5 1.8 2.2 2.4


for 0 - 18 months use 1 year, 18 - 30 use 2 year, 30 - 42 use 3 year and
42 - 54 use 4 year

so the 20 months in G2 would be the 2 year column and the date would
obviously be Feb and

the value would be 1.9 If that is ok this formula would work


=IF(OR(G2="",H2=""),"",INDEX($A$1:$E$6,MATCH(TEXT(H2,"mmm"),$A$1:$A$6,0),MAT
CH(VLOOKUP(G2,{0,"1 Year";18,"2 Year";30,"3 Year";42,"4
Year"},2),$A$1:$E$1,0)))

If you disagree with the month rounding you can change that in the vlookup
part




--

Regards,

Peo Sjoblom

Jay M said:
Chip,
I went to the website and found the double lookup and also the additional lookup options.
Will it work to do a closest match double lookup. Re: the closest match
Click to expand...
example towards the bottom of your lookup examples. This would entail
rather than the column header being fixed amounts (i.e. year 1, year 2, year
3 and the rows being the months Jan - Dec) that the cell reference would be
a period of 21 months or 38 months or whatever, where it would have to test
it to see which column to draw from and same for the month in the row, i.e.
pick what month a date falls in, such as Feb 14, 2003. I tried combining
these but ended up with #VALUE! error.
 
Yikes! Don't know why looks like it has been in a washing machine..
I always use tab to do this and this has never happened before. Nevertheless
assuming your example table has the dimension A1:E6 and the lookups are in
G2 and H2
the formula I posted will work.
 
Back
Top