Excel Lookup Series #9: INDEX 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?

I am trying to do a vlookup of sorts. In my problem I have data in columns A:Y. In column AA I have a list of values I would like to look up in column A and then return the corresponding value in column B. However, each value in column AA occurs many times in column A. I would like to return each value in column B when the value I am requesting is observed in column A. I would like to do this with no errors as well. This is what I have started but it is not working:

=INDEX($A:$Y,SMALL(IF($A:$Y=$AA$2,ROW($A:$Y)),ROW(1:1)),2)
ctrl+shift+enter

Thanks for any help

Attached is a spredsheet that I have been trying to get to work. It is using Excel 2007.

I hope I can explain this clearly on what I am trying to do. I will try to reference the cells on the spreadsheet to better communicate what I am trying to say.

If you look at sheet 2, it list employee name, supervisor, agent type, payments and connects. These numbers are going to eventually come from another report, but for now they are all manually entered, then referenced on sheet 1.

On sheet 1, what I want to be able to do is have the supervisor go to cell I1, select there name, and have the table show there employees. this part works right now. One thing I would like to have happen if possible is when a name is selected in the table, have that name populate in Cell A1, have the number in the corresponding cell in column I and Column J for that employee post to cell A4 and B4 respectively.

Now another issue I can not figure out is how to get the table on sheet 1 to show column C from sheet 2.

And finally, if a rep is B or bleneded then cell d10 should be $636.46 if they are O or outbound then cell d10 should be $411.50. I can not figure out a way to make this happen. If figured a if statement on d10 which wold reference cell I2, but it will not work, or if the selection in I2 is all it will not do anything.

As you can see I am really lost. That applied spreadshhet class I took last semster does not come inhandy here. Maybe I am asking to much.

If more info is needed please let me know

In Table 1 I have some equipment listed.

Each has an ID NUMBER, a DESCRIPTION and DATE OUT and DATE IN.

In Table 2 I want it to look up any item that has a date entered for DATE OUT and return the EQUIPMENT name next to the corresponding ID NUMBER in column B.

When a DATE IN is entered I want the EQUIPMENT name to be cleared from column B (signifying it has been returned).

So, when a date out field is the only field with a date in (for each ITEM) - the equipment name comes up against its ID number in COLUMN B in SHEET2.

SHEET1 "table1" A B C D 1 ID NUMBER EQUIPMENT DATE OUT DATE IN 2 1 iPhone 03-Oct-10 3 2 iPad 02-Oct-10 4 3 Shuffle 5 4 Nano SHEET2 "table2" A B 1 ID NUMBER EQUIPMENT 2 1 3 2 4 3 5 4

I am trying to setup 2 worksheets to create a work schedule for a small company. The first worksheet would list all employees and whether they are "in" or "off," and this I would manually input. The second worksheet would search the columns for the day and if they are "in" would pull their name and put it in the seond sheet. The second sheet would be used only for easy viewing to see who was "in" that particular day. I have been trying the VLOOKUP and INDEX/Match function with no luck. What functions should I be using?
Example:
The first is a weekly schedule for employees: the names are in column 1, the days of the week in row 1, and whether an employee is in/off in the table:
Mon Tues Wed
John In Off In
Bill Off IN In
Jane In In Off
Fred In IN IN

The second worksheet:
Mon Tues Wed
John Bill John
Jane Jane Bill
Fred Fred Fred

I hope this makes sense, and would appreciate any direction and assistance.

I have three columns of data. For example below

Row 1 (Headers): Date, Value A, Value B
Row 2: March 1, 10, blank
Row 3: March 2, 20, blank
Row 4: March 3, 25, 10
Row 5: March 4, 30, 20
Row 6: March 5, 45, blank
Row 7: March 6, 55, blank

I want to return the last non-blank value and associated date in each column.

So for Value A I want to return 55 and the date would be March 6 and for Value B, I want to return 20 and the date March 4 (obviously the value and the date are in different cells).

Thanks for any help

hi, i have a worksheet called 'data', where there are several columns, the only relevant ones being:
-- A: names
-- E, F and G: numbers

E, F and G are connected such that E must equal F+G (it's actually more complicated than that, but i'm trying to simplify it here)

I need to create a worksheet, called 'test', to ensure that, for whichever A i'm checking, E=F+G.

In 'test', I put a name in A1. I'd like to create an IF function in C1 to search data!A1 to data!A50 for the name that matches test!A1, and return 'Correct' or 'Wrong' depending on whether the respective E=F+G.

For example, if test!A1="bob" and data!A28="bob", I'd like test!C1 to return "Wrong" if data!E28 is not equal to data!F28+data!G28.

Problem is, I don't know how to put that all into a function. Any help?

- Kenneth

I am trying to match up Invoice Numbers against their appropriate Advice Note Numbers. However most advice note numbers have more than one Invoice against them.......I would like to have the result of the formula showing on one row only eg advice no 100237 Inv No 08384,Inv 10547,Inv 10102 etc The advice notes & Inv nos are held in alist in the destination file


To Summarize

Source File Result in Source File

Advice Note 100237 Inv 08384 Inv 10547 Inv 10102 Inv 10743
Advice Note 100235 Inv 11452 Inv 11552
Advice Note 100250 etc


Destination File


Advice Note

100237 Inv 08384
100237 Inv 10547
100237 Inv 10102
100237 Inv 10743
100235 Inv 11452
100235 Inv 11552

Much respect to you guys!

I have two sheets,one is daily report sheet and another is overtime sheet. I need to Lookup or search the daily report sheet for any time between 20:00 to 08:00 and record this with an X on the overtime sheet cell corresponding to the specific date and time.Any help in making a formula for this is greatly appreciated. Thank you very much in advance.
This is overtime sheet where I need tomake a formula in Cell H9 or F10.
******** ******************** ************************************************************************> Microsoft Excel - Rest Period form.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout U11 =
A B C D E F G H I J K L M N O P Q R S T U 1 **Please*mark*overtime*periods,*as*applicable,*with*an*X * * * * * * * * * * * * * * * * * * * * 2 Hours 01 02 03 04 05 06 07 08 09 10 3 * 00 01 01 02 02 03 03 04 04 05 05 06 06 07 07 08 08 09 09 10 4 Date * * * * * * * * * * 5 1 * * * X * * * * * * 6 2 * * X * X * * * * * 7 3 * * * * * * * * X * 8 4 * * * * * * * * * X 9 5 * * * * * * * * * * 10 6 * * * * * * * * * * 11 Time*format*Cell*R8*is*08:01*to*09:00*Cell*T8*is*09:01*to*10:00 * * * * * * * * * * * * * * * * * * * * Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
This is the sheet where I need to Lookup for the answers.
******** ******************** ************************************************************************> Microsoft Excel - Book11.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout D9 =
A B C D 1 DAILY*REPORT 2 PLACE* DAY DATE TIME 3 SOMEWHERE THURSDAY 1-Feb-07 04:00 4 SOMEWHERE FRIDAY 2-Feb-07 02:30 5 SOMEWHERE FRIDAY 2-Feb-07 04:05 6 SOMEWHERE SATURDAY 3-Feb-07 09:00 7 SOMEWHERE SUNDAY 4-Feb-07 09:01 8 SOMEWHERE MONDAY 5-Feb-07 03:20 9 SOMEWHERE TUESDAY 6-Feb-07 02:15 DAILY REPORT *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.