help with excel regarding vlookup etc

[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

 

[Max]

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))

 

[Max]

Oops, typo correction, sorry ..

The line

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

should read as

Let's say you have a source table (for draw qualifiers) in Sheet3:

References in the formulae all point to Sheet3, not Sheet1

hth
Max