Excel Lookup Series #6: HLOOKUP Function

Q. Is there a way of combining the =series function with the =lookup function?

In the example below I would like sheet 1, B1 to lookup the value sheet1, A1 in sheet lookup (Lookup!A1:D3) and then give the series of numbers to the right of the found result. Hopefully the figures below explain what I mean a little better.

In sheet 1:
A1 = Summer
A2 = Spring
A3 = Winter

B1=SERIES(,,Lookup!$B$1:$D$1,1) - this gives series for Summer

In Lookup Sheet:
A1 = Summer, B1 = 1, C1 = 2, D1=7
A2 = Spring, B2 = 10, C2= 22, D2=17
A3 = Winter, B3 = 0, C3= 23, D3=37

Any advice appreciated

Thanks

Joe

I have time series price data. For each date and price, I want to return the
very next date in which the price has dropped more than 10 cents.

How can I do this?

Hey guys,

Am having a bit of a problem with an HLookup. Could someone offer some assistance for me please?

I'm trying to create an IF statement with HLookups in it.

The table has 3 rows and 8 columns. In the first 2 rows are possible outcomes, of different criteria, and I'm looking for the criteria to be met in either the 1st or the 2nd Row and if so, to render the value in the 3rd row in the same column.

Thanks in advance!

Not sure how I can create a formula for my query and what function/s should be used - I have attached sample data in spreadhseet - however not sure how this will display. Hope someone can help.

ENTITY 1 Table data
Entity 1 Entity 2 Entity 3
February Asset cost January 100 90000 70000
Activity detail Asset cost February 300 6000 75000
Asset cost Asset cost March 5000 8000 80000
Holiday Pay Asset cost April 70000 100000 200000
Other Asset Holiday pay January 72450 59000 205000
Deferred Tax Holiday pay February 93890 62200 244500
Holiday pay March 115330 65400 284000
Holiday pay April 136770 68600 323500
Other Asset January 158210 71800 363000
Other Asset February 179650 75000 402500
Other Asset March 201090 78200 442000
Other Asset April 222530 81400 481500
Deferred Tax January 243970 84600 521000
Deferred Tax February 265410 87800 560500
Deferred Tax March 286850 91000 600000
Deferred Tax April 308290 94200 639500

Visualise this as two spreadsheets - the table data being my source spreadsheet
"What I am trying to achieve is if A1 is in the table data and A3 is also in the table data (A1 and A3 results are based on what is selected in a pivot table)
I want the amount that corresponds with that column and row to populate into the corresponding Activity detail in column B
eg: C5 should have 300 in it and C6 93890 and so on
If someone could assist me with a formula to resolve this I would be most grateful - also bearing in mind that for each activity there will be 12 months of data (not the 4 as in the sample) so therefore don't think the maximum nesting statements of 7 will help"

Hi All,

I am trying to import a BASE ESTIMATE table into EXCEL.

I have problems with most of the formulas, especially this one:

=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$2 2:$L$23,2)


and this one

=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10


I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?

Thank you so much in advance!

Dear all,

I am making an excel spreadsheet for 2007 and 2003. The way I am doing this is making everything in excel 2007 and save is as excel 2003 (compatibility mode). In the VBA, I wrote:

Quote:

Set Ser22 = ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(1)
Ser22.Values = Range(Cells(1, 25), Cells(n, 25))
Ser22.XValues = Range(Cells(1, 24), Cells(n, 24))
With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = Range("D16")
End With
With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = Range("D15")
End With

The code above controls a chart where the n is total points (dots) that is going to be plotted. The chart is XYscatter style (only dots, no lines).

I saved it, everything works very well!

I put a mouse scroll protection (vba code) on the sheet and protect the sheet with password. All boxes contain formulas/results are not locked.

With the protection, this sheet works fine in excel 2003, however, excel 2007 says error for:

Quote:

Ser22.Values = Range(Cells(1, 25), Cells(n, 25))

It said:

Quote:

Run-time error '-2147467259 (80004005)':

Method 'Values' of object 'Series' failed

Is this a bug in excel 2007? I know that I am going to save this worksheet as excel 2007 (.xlsm I guess if I am not mistaken with the file name), but how do I make this working?

Thank you in advance.

Hadi Sumoro

OK, so I have a number of coloums with figures.

What I want to say is:
If the column is not 0 but is a number (1,2,3...) then look to the column heading and display that heading.

Is that possible? Do I need a lookup for that one?

Hi - thanks in advance to anyone who can help with this! It's taken me over a week off and on to fathom and still I am stuck!

If you love a challenge then this is for you!

Basically I have a list of employees who each have a 10 week work rota - some are the same as others but I have over 600 unique rotation plans between 6,000+ staff members

I have identified what their plan looks like (don't ask me how but I have and I don't want to go back and start again) - I have also identified what the plan they are on is to be called -

What I can't do is work out who is on which plan

In short...

I have a Rotation Plan name with a Rotation Plan description next to it
Employee numbers with Rotation Plan descriptions next to them
I need to get the Rotation Plan name next to the employee numbers

vlookup won't work because the description is too long I believe - it just won't let me do it

I've tried replacing common characters in the description with numbers so as to create a number and use lookup that way but that won't work either - it gives the same number to different descriptions!

I hope this is clear

Attached are the two tabs which I need to relate to eachother - with the Rotation Plan Name in the the PLAN tab next to the Employee Number in the EMPLOYEE tab

Fire away if not clear

Thanks again - Paul

Any ideas with the below. I am trying to countif "holidays" between two dates, and produce the answer in the yellow box. Was trying to do it via hlookup, various if's etc but i cant seem to get it to work.

On the below between the perameters below Rob would = 2 and Kev = 0 and Pete = 1.

_______________ 01/02/10 02/02/10 03/02/10 04/02/10
Pete _____________ Holiday Holiday
Rob ____________________ Holiday Holiday
Kev _____________________________________ Holiday


answer between 02/02/10 & 03/02/10

Pete =
Rob =
Kew =

Is lookup/ hlookup the way to do it with < and >, or would you suggest someother way.

Need a solid formula so if i change the perameters in the answer box, the figures would change accordingly