Using lookup formula

[k.roberts]

I need to find a value in a table where value in Column A = "example"
and date across Row 1 = "Aug-08". I am summarising data in a simple
table in my first excel sheet and have got a dropdown listbox for the
month, fixed fields for the values in column A.

Table layout is as below:-

A B C D
Aug 08 Sep 08 Oct 08
Utilities 100.00 110.00 120.00
Rent 550.00 550.00 560.00

I understand that in the basic VLOOKUP
(lookup_value,table_array,col_index_num,range_lookup) column index
number can be changed so 2 is Aug and 3 is Sep and so on. But I now
need to 'nest' a second lookup to return data from Aug 08 column when
I select Aug-08 in my dropdown.

help!

 

[Bernard Liengme]

I will assume you Aug 08, Sep 08 are in B1, C1 etc
Suppose in Z1 you have "Aug 08", the MATCH(Z1,B1:J1) will return the postion
in the array that matches Z1, in this case 1
So your VLOOKUP uses
VLOOKUP(lookup_value,table_array,MATCH(Z1,B1:J1) +1,range_lookup)


However, I am assuing yhe data is text. If it is date type then Z1 must have
the same datatype as B1:J1
best wishes

 

[k.roberts]

I will assume you Aug 08, Sep 08 are in B1, C1 etc
Suppose in Z1 you have "Aug 08", the MATCH(Z1,B1:J1) will return the postion
in the array that matches Z1, in this case 1
So your VLOOKUP uses
VLOOKUP(lookup_value,table_array,MATCH(Z1,B1:J1) +1,range_lookup)

However, I am assuing yhe data is text. If it is date type then Z1 must have
the same datatype as B1:J1
best wishes
--www.stfx.ca/people/bliengme









- Show quoted text -

Perfect, thanks very much!