Lost in the lookup!

G

Grandma B

I need HELP. Trying to self-teach and am unable to create lookup table
as directed in Case Problem 2 page EX294 in Excel 2003 2nd Edition (
step 4 ) Someone PLEASE respond. I'm a grandma trying to figure out how
to work on the computer by myself.

Grandma B
 
P

Pete_UK

I don't know which book you are referring to, but to set up a lookup
table you would normally have at least two columns of data - imagine
you had this:

John 12/06/1995
Naomi 10/02/1997
Fred 05/10/1994
Andrea 07/02/1997
Helen 09/03/1999
David 03/08/2001

which might be the dates of birth of your grandchildren. Imagine you
have entered this data in cells L1:M6 of your worksheet. In cell B1 you
could enter this formula:

=IF(A1="","",VLOOKUP(A1,L1:M6,2,0))

And then in A1 you could enter one of the names above and it would
return the appropriate date of birth, or give you a blank cell if A1 is
blank.

There are 4 parameters in the VLOOKUP function:

* A1 (above) refers to the cell that contains the data you want to
use as the key to your lookup table.

* L1:M6 (above) refers to where the table is - sometimes this might
have to be written as L$1:M$6 if you want to copy the formula down, and
sometimes even as Sheet2!L$1:M$6 if the table itself is on a different
sheet. The data which you are trying to match with A1 must be in the
first column of this table.

* 2 (above) relates to the column in the table where you want to
return data from if you find a match.

* 0 (above) could also be entered as FALSE and must be used if the
data in the table is not sorted in order.

Hope this gives you some general help - post back if you need something
more specific.

Pete
 
V

vlook fomula

Sheet A has
Col A: Inventory code
Col B: Description
Col C: Quantity

Sheet B has
Col A: Inventory code
Col B: Description
Col C: Quantity ( both sheets have same data but inventory code may be
different)

Sheet C required
Required: total quantity of sheet A and sheet B in Col. C of sheet C

Kindly help me to create a logical formula / vlook formula

Example

Sheet A

Col A Col B Col C
Inventory code Description Quantity
10001 pencil 25
10002 pen 50
10003 rubber 75

Sheet B
Col A Col B Col C
Inventory code Description Quantity
10001 pencil 25
10002 pen 50
10004 Duster 15


Sheet C
Col A Col B Col C
Inventory code Description Total Qty of sheet A & Sheet B
10001 pencil ? ( total required with the help of vlook formula)
10002 pen ?
10003 rubber ?
10004 Duster ?

Zafar
 
P

Pete_UK

You've hi-jacked Grandma B's post, but try this in C2 of Sheet C:

=IF(ISNA(VLOOKUP(A2,'Sheet A'!A$2:C$1000,3,0)),0,
VLOOKUP(A2,'Sheet A'!A$2:C$1000,3,0)) +
IF(ISNA(VLOOKUP(A2,'Sheet B'!A$2:C$1500,3,0)),0,
VLOOKUP(A2,'Sheet B'!A$2:C$1500,3,0))

This is all one formula - I've split it to avoid awkward line breaks.
I've assumed you have a table of 1000 items in Sheet A and 1500 items
in Sheet B, so adjust these values to suit your data, then copy the
formula down.

Hope this helps.

Pete
 
Top