Excel Lookup Series #8: MATCH 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?

I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.

For example, the data source contains different pets, their names, ages and their owners.

The other sheets are on a one-per-owner basis.

What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:

John Smith (in cell A1)

Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4

I am guessing I need to incorporate a further function into the VLOOKUP function but I cannot figure out which one!

I want to create a formula under (Region) title/column to match (State) field.
For example: if (A5)= NJ or NY, then (region) will be (NE), IF (A5)= AZ or CA, or NV, then (Region) will be (west),so on & so forth.

I don't want to create a seperate table for data to be looking at & match fields

Please see attached. Thanks

So I am having a bit of trouble matching partial words. I have research for a few hours and tried a variety of formula's (isnumber(search), index(match), etc) but haven't seem to found the one that works. Basically, I have to extract partial words out of a set and match it to another set. For example:

Column/Row - Nomenclature
A1. 001 - Army
A2. 002 - Navy
A3. 003 - Marines
A4. 004 - Air force

A5. AA - Navy
A6. AB - Air force
A7. AC - Army
A8. AD - Marinies

This is all on the same worksheet and in fact in the same column. My logic is, if Column A1 Contains(Army), then find the corresponding wording in (A5:A8) and extract the first two letters And place in column B1. The answer should be AC. Can anyone help me formulate this?


Thanks,

Steve

I am trying to find a part# that is in column F, I need to lookup from from sheets 1 - 6 in column F.. when found I need the data from column G.

Thank you for your help.

Hi,

I am using Vlookup to match a string, which is the lookup Value and get the value of it's count (from Col B), However the match seems to go awry and the Lookup function ends up matching the wrong strings.


Vlookup used like

=VLOOKUP(A2,'[default.xls]Old_exception_list

With these functions they find the highest value which is less than or equal to the lookup value. For example

A B
1 30
2 60
3 70
4 80
5 100

If I want the lookup the 'A' value of 66 it will return '2'.

I want to lookup the highest value ie anything over 60 return value of 3.

Anybody have any ideas ?

Simon

I have a sheet of names (first name A11 and last name B11). There are many columns of data after the names, but the last column is AG which has notes and comments. I have decided I want to move the notes to another sheet. So I will have on the new sheet at column A5 first name and B5 last name anc C5 Notes.

I would like to create hyperlink to the notes from the first sheet. I tried using Named reference, but I have close to 2000 names anc counting. I looked at HYPERLINK, but I can not get the lookup and or match to work. I have two ways I would like it to to work:

1. I believe this is the hardest of the two. If I select the name on the first sheet that takes me to the corresponding notes on the 2nd sheet.

OR

2. In column AG of Sheet1 I have the text "See Notes" which is a link to the the corresponding notes on the 2nd Sheet.

Also I need the links to be maintained when I sort the rows on the first sheet.

Thanks for any help.