help with excel regarding vlookup etc

  • Thread starter Thread starter rod
  • Start date Start date
R

rod

hi i have a file at the moment. its a weekly money draw.

2nd sheet needs to hold the winning numbers, winning
amounts and person who has won.

all i want to do is when i put the number into the table
then the program will automatically tell me who has won in
the next column

can anyone help me with this problem

cheers

rod
 
One approach using OFFSET and MATCH:

Let's say you have a source table (for draw qualifiers) in Sheet1:
(with data in row2 down)

col A = Name of Customer
col B = Amount of purchase
col C = Unique serial number (assigned)

In say, Sheet2
------------------
Here's where you put the draw results, e.g.:
(with inputs of lucky numbers drawn in A2 down,
returns by formula in B2 and C2 down)

col A = Winning serial numbers
col B = Winner's name
col C = Prize Amount*

*Assume prizes are say, $Cash = Amount of purchase

Put in B2:
=OFFSET(Sheet3!$A$1,MATCH($A2,Sheet3!$C:$C,0)-1,COLUMN()-2)
Copy across to C2, then down as many rows as you have inputs in col A

----------------------
You can also use an IF(ISNA(...),< Alert Message >,(...)) construct
to give alert messages for cases of no match found, viz:

Try instead, in B2:
=IF(ISNA(OFFSET(Sheet3!$A$1,MATCH($A2,Sheet3!$C:$C,0)-1,COLUMN()-2)),"No
match !",OFFSET(Sheet3!$A$1,MATCH($A2,Sheet3!$C:$C,0)-1,COLUMN()-2))
 
Oops, typo correction, sorry ..

The line
Let's say you have a source table (for draw qualifiers) in Sheet1:
Click to expand...

should read as
Let's say you have a source table (for draw qualifiers) in Sheet3:
Click to expand...

References in the formulae all point to Sheet3, not Sheet1

hth
Max
 
Back
Top