lookup

[Diana]

I have done a subtotal so that the spreadsheet (lets call it Sheet1) looks
like so:

Apple Total 6
..
..
Orange Total 7
..
..
Cherry Total 10
..
etc...

I have a table in another worksheet that shows:

Fruit MaxNum
Apple 50
Orange 34
Cherry 23
etc. etc.

I want to perform a lookup so that it puts the MaxNum of each fruit in
Sheet1, 2 columns along, on the Total row, so I end up with:

Apple Total , 6, , 50
..
..
Orange Total, 7, ,34
..
..
Cherry Total, 10, ,23

Thanks in advance!

 

[Stefi]

The formula is
=VLOOKUP(SUBSTITUTE(A4," Total",""),Sheet3!A:B,2,FALSE)
where Sheet3 is the location for Maxnum table,
A4 is the cell containing Apple Total

Enter it in C4 (next to Apple total 6)!
Now set outline level to 2>select all Total rows in column C (by dragging
from C4 to C10)>Edit>Goto>Visible cells only>Fill down (Ctrl+D)

--
Regards!
Stefi



„Diana†ezt írta: