Excel Lookup Series #1: VLOOKUP Function 1st Example

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?

Hi

Just need some help as to which function I use. I have a figure in say A1
that I want to match to the same figure in another worksheet in column A &
return what the figure is in column B to show in A2. I've been using the
vlookup and lookup functions but just seem to get back #N/A.

Hope it makes sense, help would be much appreciated.

Hey All,

So i have been put to this challenge to do a inventory tracking sheet for my work and keep coming to dead ends. I do appreciate everyones help especially Leith Ross, Thanks again!

Here is what i am after:

I have 2 sheets, 1 has Item number, description and how many parts on hand for columns, The other has the same but with cost per unit added. Is there a way that i can use a find function of some sort to look up in the second sheet using the item number from the first (which is the same as the second) to find the cost per unit?

Thanks and much appreciated
Mikey

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

I "inhereted" a graph with both LINE and BAR series.

I want to add another LINE series.

How?

I can add a BAR. I try to change to a LINE but no go...

Ok excel is pissing me off you guys....here's the situation:

i set H2 cell to be =LOOKUP(C2,P2:P45,Q2:Q45)

basically, cell H2 is supposed to be the GDP in numbers that correspond to the country in C2.....

Here's what the P and Q cells look like (delimited by the colon)

P Q
United-States:13860 <--here's row 2
Mexico:1353 <--here's row 3 etc
India:2965
Sweden:333.1
Germany:2883
Russia:2076
China:7043
Hong-Kong:293.4
France:2067
Saudi-Arabia:572.2
Canada:1274
Kuwait:138.6
Egypt:431.9
Italy:1800
Brazil:1838
Chile:234.4
Czech-Republic:249.1
United-Arab-Emirates:145.8
Cyprus:21.41
Taiwan:690.1
Ukraine:321.3
Netherlands:638.9
Malaysia:357.9
Singapo 222.7
Indonesia:845.6
Portugal:232
Japan:4417
South-Korea:1206
Israel:184.9
Denmark:204.6
Ireland:187.5
Colombia:320.4
New-Zealand:112.6
Venezuela:335
Turkey:667.7
Switzerland:300.9
Spain:1362
Kazakhstan:161.5
Belgium:378.9
Thailand:519.9
AUstralia:766.8
Romania:246.7
Lebanon:40.65
Iceland:11.89 <--here's row 45

but then the lookup function in cell H2 returns 11.89 even though C2 is "United-States".........it's supposed to return 13860....

can any of you tell me what I did wrong?

When using the lookup function in Excel 2003, it would be extremely helpful
to have an error appear if the looked up item does not appear in the list.
Example: If I have a small list of students in list A and I'm trying to look
up the corresponding teacher in list B, it returns a teacher for every
student in list A, even if that student is not in list B, and therefore, it
is the wrong information. I would rather have it display an error message,
so that I know that something is missing.

hi , i have data stored in excel as column a- Purchase order no column b-
Invoice no i want to query basis purchase order no & result should give
mutiple invoice no stored agst one purchase order no how do i do this using
lookup or something !