Excel: Multiple VLOOKUP and IF/AND

Excel Excel 2007
July 12, 2010 at 02:09:29
Specs: Windows XP
I am trying to extract some data using VLOOKUP and IF/AND, but I am having no success.

Column F is the date. Column G is the machine number. Column O is minutes. Column Q is a quantifier specifying the condition of the machine.

For example, if on 6/21/10 I have condition 3, 5, or 10, then I want the machine number to be recorded in a separate worksheet in Column A and the time recorded in Column B. This must be separately listed for each separate incident whereby any machine on a particular day has condition 3, 5, or 10.


See More: Excel: Multiple VLOOKUP and IF/AND

Report •

July 12, 2010 at 04:38:57
The or formula returns a true or false.
OR(Q2=3,Q2=5,Q2=10) will return true if Q2 is either one of the three conditions

If(OR(Q2=3,Q2=5,Q2=10) , vlookup,"")

Will result in the vlookup, else if will return blank.

The way you want the solution to show up is not very clear... what are you looking up? if say row 3,10 and 15 match your or statement then using formulas will give you values in those rows in sheet2... no way to list them in adjacent cells without a very complex formula or VBA.

If you're looking for Sheet 2 Row 3 looks at Sheet 1 Row 3 then use
Sheet 2 Cells A3 --> If(OR(Q3=3,Q3=5,Q3=10) , G3,"")
Sheet 2 Cells B3 --> If(OR(Q3=3,Q3=5,Q3=10) , O3,"")

Report •

July 12, 2010 at 05:06:30

In a column to the left of the date, say column E, on the first row with data, enter this formula:
(There needs to be at least one row above the first data entry.
In this example the formula was placed in cell E2
Now drag this formula down to extend it alongside all rows containing data. You can go beyond the last row of data, so that new machine data can be entered without having to add this formula each time.

Note that if you inserted a new column on the source worksheet, to contain these formulas, you will need to change the reference to column Q to column R.

Column E will now have a sequential number for each time there is one of the three codes, 3, 5 or 10.

On the other worksheet in column A, say cell A2, enter this formula:
=IF(ISNA(VLOOKUP(ROW()-1,Sheet2!$E$1:$Q$22,3,FALSE)),"", VLOOKUP(ROW()-1,Sheet2!$E$1:$Q$22,3,FALSE))
Change both end range values ($Q$22), to match the end of data on the source worksheet, and change the two worksheet names to match the name of your source worksheet. The end range can be further down the source worksheet than the current last data entry, to allow for new data to be added later).
If you start this formula on a different row, then you will have to change the two values "-1"
If you start in cell A3, then use -2

Drag the modified formula in cell A2 to cell B2, and change the column offset value from 3 to 11. The result will now be the minutes column (Col. O) rather than the machine number (Col. G).
Drag both formulas down as many rows as you will have machine codes - then carry on to add a margin of safety.
Excess rows of these formula will remain blank.

Columns A and B now have the results for all codes 3, 5 or 10, with no empty lines between the results.


Report •

Related Solutions

Ask Question