Lookup in Excel

[DanFox]

I have a spreadsheet:

Worksheet 2
A B C D
1 Date Item Price Total
2 1st Apr Widget £1 £3
3 2nd Apr Brush £2 £2
4 3rd Apr Brush £2 £0
5 4th Apr Widget £1 £5


I would like to create a formula in worksheet 1 that returns the data
held in columns A, B, C and D of worksheet 2 - but only where the
value in D is greater than 0.

If the formula works, worksheet 1 should appear as:

A B C D
1 Date Item Price Total
2 1st Apr Widget £1 £3
3 2nd Apr Brush £2 £2
4 4th Apr Widget £1 £5


I know I need some kind of lookup statement, but can someone help as I
don't understand arrays or vectors.

Thanks

 

[Arvi Laanemets]

Hi

One way is to use helper column on sheet with original data, and then use
VLOOKUP on sheet with non-zero totals to retrieve according records. An
example for such routine is described in my answer to thread 'Easy formula
for experienced users' at September, 03.2004 - you can adjust it for your
needs easily.

Another way is to create an ODBC query, which retrieves all records with
Total>0

And you can use also advanced filtering, to copy rows with Total>0 to new
destination, but this doesn't appply, when you'll add/remove/edit data in
original table later.

 

[DanFox]

Does anyone have a suggestion for the lookup statement?