lookup
 

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!

lookup
 

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 2select all Total rows in column C (by dragging
from C4 to C10)EditGotoVisible cells onlyFill down (Ctrl+D)

--
Regards!
Stefi



€žDiana€� ezt Ã*rta:

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!