Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP needing to match two cells
I have a worksheet with data. I need to match the date and the shift and
return the value to a summary page. Sample data: Date Shift AvailMin Oper_BreaksOper_StickerMach 2/9/2009 2 480 60 0 5/5/2006 2 480 0 0 5/5/2006 1 480 24 14 So, I need to lookup Date and Shift and return to the cell the available minutes. Sheet sample of where data goes to: 5/9/2006 DAILY Operational Shift 1 Shift 2 Breaks (value of 24 would go here) Sticker Machine Breakdown Infeed Breakdown Hoist Infeed Lug Loader Infeed Moisture Meter Infeed Tipple Infeed Table -- ----- Thank you, Liz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP needing to match two cells
If that 24 is in column D and the sheet name is "Sheet1", for example, and if
the date 5/5 is in cell C2 of the Summary sheet, (lots of assumptions!) then this formula in the Summary sheet would work: =INDEX(Sheet1!D:D,MATCH(C2&"1",Sheet1!A1:A100&Shee t1!B1:B100,0)) This formula needs to be ctrl/shift/entered, not simply entered. the C2&"1" is because C2 contains 5/5/2006 and the "1" is the shift. HTH "Liz Steffen" wrote: I have a worksheet with data. I need to match the date and the shift and return the value to a summary page. Sample data: Date Shift AvailMin Oper_BreaksOper_StickerMach 2/9/2009 2 480 60 0 5/5/2006 2 480 0 0 5/5/2006 1 480 24 14 So, I need to lookup Date and Shift and return to the cell the available minutes. Sheet sample of where data goes to: 5/9/2006 DAILY Operational Shift 1 Shift 2 Breaks (value of 24 would go here) Sticker Machine Breakdown Infeed Breakdown Hoist Infeed Lug Loader Infeed Moisture Meter Infeed Tipple Infeed Table -- ----- Thank you, Liz |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP needing to match two cells
=INDEX(Minute_Range,MATCH(1,(Date_Range=lookup1)*( Shift_Range=lookup2),0))
entered with ctrl + shift & enter -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Liz Steffen" wrote in message ... I have a worksheet with data. I need to match the date and the shift and return the value to a summary page. Sample data: Date Shift AvailMin Oper_BreaksOper_StickerMach 2/9/2009 2 480 60 0 5/5/2006 2 480 0 0 5/5/2006 1 480 24 14 So, I need to lookup Date and Shift and return to the cell the available minutes. Sheet sample of where data goes to: 5/9/2006 DAILY Operational Shift 1 Shift 2 Breaks (value of 24 would go here) Sticker Machine Breakdown Infeed Breakdown Hoist Infeed Lug Loader Infeed Moisture Meter Infeed Tipple Infeed Table -- ----- Thank you, Liz |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP needing to match two cells
Hi Bob,
You have been very helpful. Here is my syntax and it's not working. =INDEX(Header!D:D,MATCH(A2&"1",Header!A2:A700&Head er!B2:B700,0)) Can you help some more? -- ----- Thank you, Liz "Bob Umlas, Excel MVP" wrote: If that 24 is in column D and the sheet name is "Sheet1", for example, and if the date 5/5 is in cell C2 of the Summary sheet, (lots of assumptions!) then this formula in the Summary sheet would work: =INDEX(Sheet1!D:D,MATCH(C2&"1",Sheet1!A1:A100&Shee t1!B1:B100,0)) This formula needs to be ctrl/shift/entered, not simply entered. the C2&"1" is because C2 contains 5/5/2006 and the "1" is the shift. HTH "Liz Steffen" wrote: I have a worksheet with data. I need to match the date and the shift and return the value to a summary page. Sample data: Date Shift AvailMin Oper_BreaksOper_StickerMach 2/9/2009 2 480 60 0 5/5/2006 2 480 0 0 5/5/2006 1 480 24 14 So, I need to lookup Date and Shift and return to the cell the available minutes. Sheet sample of where data goes to: 5/9/2006 DAILY Operational Shift 1 Shift 2 Breaks (value of 24 would go here) Sticker Machine Breakdown Infeed Breakdown Hoist Infeed Lug Loader Infeed Moisture Meter Infeed Tipple Infeed Table -- ----- Thank you, Liz |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP needing to match two cells
using 2 worksheets. first is the bid sheet, second is the database.
I need to find a set value from a database which will first lookup a part number/name and then, depending on an entered letter sequence, return a cost. Think of ordering a car "A", and having options for certain tires. The letter sequence, lets say "t1" would return a cost for tires accordingly but based on only car "A" I first choose a part from a drop down box (col b). in col C a price is generated via lookup from col B. in col D I might need an assembly item for that paticular part which comes in several types. In col A I want to enter a letter sequence which row D will recognize by first matching col B in a database, then depending on the "letter sequece" will return a value (cost). Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky ... Pullout related rows that match a single word using Vlookup | Excel Discussion (Misc queries) | |||
can vlookup be forced to make a case sensitive match? | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
is there a way to search with vlookup to match more than 1 column | Excel Discussion (Misc queries) | |||
How to match two different cells text, if the text is not in the . | Excel Worksheet Functions |