HLOOKUP Problem

[Sharon Hague]

Hi

I have a worksheet consisting of data in 4 rows and 10 columns

In rows 1 and 3 across 10 columns I have dates which are formatted to dd/mm/yy and displayed underneath in rows 2 and 4 accross 10 columns I have number fields.

i.e
A B C ETC....

Row 1 01/04/06 02/04/06 03/04/06
Row 2 1 0.5 0
Row 3 01/05/06 02/05/06 03/05/06
Row 4 0 1 0.5

I am trying to use HLOOKUP to search for data in the above table to return the data displayed in rows 2 and 4 dependant on the date, but I can't ask it to use A1:C2 and A3:C4 in the table array. Can I only have 1 table array?

On another worksheet I want it to return the following from the data entered above: -

01/04/06 1
02/04/06 0.5
03/04/06 0
01/05/06 0
02/05/06 1
03/05/06 0.5

Is there any way to do this or maybee another fundction is used?

I'd appreciate anybody's help on this.

 

[shades]

I think you will need to use the INDEX/MATCH approach rather than lookup. Do you know how to do that?
________
Aprilia Etv1000

 

[Sharon Hague]

Hi

No I havn't used or heard of that before.

Is it quite straight forward and can I find this in help?

 

[Sharon Hague]

Hi Shades

I have just had a look at the index way and I don't think it is going to work.

I have attached an example worksheet of what I am looking for.

I hope its pretty straight forward.

Cheers.

 

[shades]

I don't see the attachment
________
Pronto

 

[Sharon Hague]

Sorry

Try now.

 

[shades]

Okay, it can be done. I have used Dynamic named Ranges, in case rows 1:2 and 4:5 will be longer (more columns).

Data1 is defined as:

=OFFSET(Work!$A$1,0,0,2,COUNTA(Work!$1:$1))

Data2 is defined as:

=OFFSET(Work!$A$4,0,0,2,COUNTA(Work!$4:$4))

Mon1 is defined as:

=OFFSET(Work!$A$1,0,0,1,COUNTA(Work!$1:$1))

Mon2 is defined as:

=OFFSET(Work!$A$4,0,0,1,COUNTA(Work!$4:$4))

Then in cell B17 put this formula (and copy down to B23):

=IF(MONTH(A17)=1,INDEX(Data1,2,MATCH($A17,Mon1,0)),INDEX(Data2,2,MATCH($A17,Mon2,0)))

This checks cell A17 to determine the month. Once it determines the month, then it looks up the appropriate data set and the appropriate month.

This has the advantage that you can add columns to your two data sets without changing the formulas. And if you have another data set beginning in row 7, you can add the Data3 named range, and Mon3 named range and add another IF clause to this formula.
________
Herbal vaporizers

 

[Sharon Hague]

Hi

Many thanks for your help on this. It looks as though it will work great, however, I have added some more rows for months 3 and 4 underneath the others, defined the ranges but when I come to change the if function it doesn't work.

I have added just the 3rd index to see if it worked but it doesn't like it. I have attached the file as it looks now showing the if function in cell B29. My thoughts were that when adding another index to the if function that at the end of the function there should be 4 )))) but it will only allow me to have 2 )) as it states there are too many arguments. When reducing this to 2 )) it returns a figure of N/A in cell B29.

I have had a play around with the brackets but still can't get it to work. I'm sure its due to where these are placed.

I'd appreciate your help on this if you could have another look at it for me.

Cheers.

 

[shades]

I will take a look in a couple of hours. But some questions. How many more rows will you be adding? 12 total for the number of months? What about beyond that year?

It might be better for a slightly different but more efficient set up. I will work on that set up while you respond to these questions.
________
VAPORIZER ANSWER

 

[Sharon Hague]

Hi

Yes 12 would be best.

Beyond 12 doesn't matter as I will just use the same worksheet for future years.

Many Thanks

 

[shades]

I'm not satisfied with this, but it includes all 12 months (on one worksheet labeled Data), and then on Display worksheet, the lookup table and two sets of formulas in columns C and D, then a summary formula in column B. Change the date in column A and you will see the corresponding data change in column B.

This is a quick fix which works, but it can be simplified. But I won't have time until later today.
________
VOLCANO VAPORIZER

 

[shades]

I haven't had time to look back at this. Will this work for you, or do you need another approach?
________
SH-AWD