Excel Lookup Series #7: LOOKUP Function

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?

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

Hi,

I'm trying to create a custom function that will basically do a vlookup and return a result from the same table everytime, but that is not included in the workbook.

For example today someone sends me a file, in column B is a product code, I open up a workbook that contains a master list of product codes and prices, and use the vlookup function in the file I received to lookup the price from my workbook that contains the prices.

Example:
in received workbook cell B6 I type:
=VLOOKUP(B5,'[Master File.xls]Sheet1'!$K$2:$L$19751,2,FALSE)

I'm looking for a way to store the lookup table that is currently in [Master File.xls]Sheet1'!$K$2:$L$19751 somehow in VBA so that I could write a custom function such as:

=CustomFunction(B5)

that would return the lookup value from the second column of my prices table, but I don't want to have include the table in the workbook that I'm working on. Is there a way to store a worksheet associated with a custom function in the personal macro workbook, or as part of an add-in ? Or any other way that anyone knows of to do something like this?

If anyone can get me pointed in the right direction on this, it would be very much appreciated.

Thanks,

Can pivot table do a lookup without using the lookup function - displaying
data whether or not it exist in the other worksheet being reffered to? I am
using excel 2003.

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 there,

Is there an excel function that will allow you to look at the column and row headers and pull the cell (number) that it intersects?

Thanks,
Simon

I need an excel form which could display the matching data entered in TextBox.

Below is what I Have

A workbook having Sheet1 as database
Column1 : RollNo
Column2 : Full Name
Column3 : Address
Column4 : DOB
this workbook having about 500 records.

What I need

A standalone UserForm in Sheet2 having a text box and SEARCH button.

As text (rollNo.) entered in text box, the form should lookup the rollNo in sheet1 and display Full Name, Address and DOB below it.

Can Anyone Help me.

Wondering if someone could help:

I have a database with a 'MAX' function to the right of it. I am trying to lookup that max value in the row and return the title in row 1 that it corresponds to.

Failing that, I would like to return the actual column that the corresponding number is in so I can then write another formula using that colomn reference point.

Can anyone please help?

Thanks in advance!!

Good day excel brothers, seniors & juniors

let's get str8 to the point.
My data sheet comprises of projects in MW. So I have the name of a project and there power in MW.
I would like to fetch all projects that have a similar MW power within a range of + and - 100, and align them one under another.
i.e.:

project 1 = 1000 MW; year=2007
project 2 = 1200 MW; year=2008
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009
project 5 = 800 MW;year=2009

I need a formula that gives me fetches all projects that are =1000 or + and - 100 MW
the result should be the following :

project 1 = 1000 MW; year=2007
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009

I hope I made myself clear...in any case...I'll pass the torch to the Master that can work this dilemna out.
Tons of thanks in advance !!