Excel Formula

[jabbaman]

Hi all, I'm new to MrExcel
I'm trying to work out which input to use for this scenario.
In cell A2 i have 4 different win prices i can use
So what i want to be able to do is when i enter a price in A2 it will automatically put the outlay amount in cell B2 and in cell C2 when i enter 1st i want it to automatically enter the amount from cell D2 into E2 , obviously if the result in C2 is either 2nd,3rd or Unplaced it would automatically enter $0.00. I hope someone can help me with this, thanks.
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1

Win Price	Outlay	Finished	Win Amount	Won/Lost	Difference	Total Outlay	Profit/Loss	Prices Min	Prices Max	Bet	Position
$1.55	$250.00	1st	$387.50	$387.50	$137.50	$250.00	$137.50	$1.00	$2.00	$250	1st
$1.80	$250.00	3rd	$450.00	$0.00	-$250.00	$500.00	-$112.50	$2.01	$3.00	$200	2nd
$2.70	$200.00	1st	$540.00	$540.00	$340.00	$700.00	$227.50	$3.01	$4.00	$150	3rd
$7.00	$100.00	Unplaced	$700.00	$0.00	-$100.00	$800.00	$127.50	$4.01	$150.00	$100	Unplaced
$3.40	$150.00	1st	$510.00	$510.00	$360.00	$950.00	$487.50
			$0.00		$0.00	$950.00	$487.50
			$0.00		$0.00	$950.00	$487.50

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Michael M]

So this in B2 and drag down

=IF(A2<=2,250,IF(A2<=3,200,IF(A2<=4,150,100)))

 

[jabbaman]

You are a legend Michael, thanks very much i really appreciate it.

 

[Michael M]

And just in case A2 is blank

=IF(A2="","",IF(A2<=2,250,IF(A2<=3,200,IF(A2<=4,150,100))))

 

[jabbaman]

Cheers Michael

 

[jabbaman]

Hi Michael,
I have one more formula i need help with, i think it might a IF formula not sure. So A2 has dog names, B2 has trainers name, i also have columns with each trainers dogs, so what i would like to do is once i put the dogs name in A2 it automatically put the trainers name in cell B2. Hope you can help, thanks.

TIPPERARY MEL	Sam Sultana	Ashlee Terry	BIG REPUTATION	Brooke Ennis	BAROOGA BRETT	Kevin Eyles	BIG BARRA	Darren Brown	CHIEF'S EMPIRE	Seona Thompson	ASTON DEE BEE
MINI DIVA	Sam Sultana		BRIGHT NEW SHINE		BELLA SHIMA		HEARTBREAK HERMY		CHIEF'S SIDEKICK		ASTON DUKE
YODA	Brooke Ennis		BURN LIKE FIRE		BELT UP BUBBS		KRAKEN AYE KAY		MAYOR DIGBY		AZKABAN
BAROOGA BRETT	Brooke Ennis		CHARMED AN TAKEN		MIGHTY HAZZA		KRAKEN BAM BAM		MINI DIVA		BECKHAM
INVASION	Karen M Walsh		EYE GOT IT		MY MATE LENNY		KRAKEN BOLT		PERCY'S CURSE		BEWILDERING
THRILLING ARNOLD	Karen M Walsh		EYES ON YOU		PANDAKEN		KRAKEN GRETA		PERCY'S EMPIRE		DESPACITO
RIDIN' SHOTGUN	Karen M Walsh		LEICA CALI KING		ROCKSTAR STRATUS		KRAKEN PACE		YOUR HANGER		DOUBLE QUE
HOT PLATTER	Johannes Vanderburg		MY AMERICAN GIRL		STERLING ARCHER		MI ROMA				EQUANIMITY
DIDDILEE	Johannes Vanderburg		NERVOUS AN WEIRD		TRITT TRITT		WHAT STOPPING YA				EXTREME MAGIC
KRAKEN PACE	Kevin Eyles		OUTTA MY DEPTH		TRUMPSTA						FAB REIKO
KRAKEN BOLT	Kevin Eyles		PURE WHITE EVIL		YODA						FLYING SPARKLES
KRAKEN AYE KAY	Kevin Eyles		QUENA DEE AIR		ZIPPING ANGUS						HEMSWORTH
KRAKEN BAM BAM	Kevin Eyles		SHAKIN' BRAVE		ZIPPING NANCY						OUT OF RANGE
HEARTBREAK HERMY	Kevin Eyles										POKE THE BEAR
PERCY'S EMPIRE	Darren Brown										REIKO ROCKETTA
CHARMED AN TAKEN	Ashlee Terry										STYGIAN
TRUE TALENT	Seona Thompson										TRUE TALENT

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Michael M]

Do you have a lookup list for trainers / dog names ?
If so, where is it ?
If not, how do we know who trains which dog to start with??

 

[jabbaman]

Hi Michael,

no i haven't got a lookup list. All i have is their name in one cell and their dogs in the next cell.
Example:
Kevin Eyles BIG BARRA

HEARTBREAK HERMY
KRAKEN AYE KAY
KRAKEN BAM BAM
KRAKEN BOLT
KRAKEN GRETA
KRAKEN PACE
MI ROMA
WHAT STOPPING YA

 

[Michael M]

OK, is the name of the trainer next to each dog ??
like this

Excel 2007
	A	B
1	Kevin Eyles	BIG BARRA
2	Kevin Eyles	HEARTBREAK HERMY
3	Kevin Eyles	KRAKEN AYE KAY
4	Kevin Eyles	KRAKEN BAM BAM
5	Kevin Eyles	KRAKEN BOLT
6	Kevin Eyles	KRAKEN GRETA
7	Kevin Eyles	KRAKEN PACE
8	Kevin Eyles	MI ROMA
9	Kevin Eyles	WHAT STOPPING YA
Sheet1

 

[jabbaman]

Hi Michael, yes but i only have his name once in A1 then the dogs the way you have it. but i have a few different trainers , so another trainers name in C1 then dogs name D1 etc etc E1, F1 and so on.

 

[Michael M]

with 'em all side by side it's gonna be difficult to write a simple formula.
I would suggest a lookup table, either somewhere on the same worksheet OR on it's own...and then use a VLOOKUP formula
The table should look like this

Excel 2007
	D	E
1	BIG REPUTATION	Ashlee Terry
2	BRIGHT NEW SHINE	Ashlee Terry
3	BURN LIKE FIRE	Ashlee Terry
4	CHARMED AN TAKEN	Ashlee Terry
5	EYE GOT IT	Ashlee Terry
6	EYES ON YOU	Ashlee Terry
7	LEICA CALI KING	Ashlee Terry
8	MY AMERICAN GIRL	Ashlee Terry
9	NERVOUS AN WEIRD	Ashlee Terry
10	OUTTA MY DEPTH	Ashlee Terry
11	PURE WHITE EVIL	Ashlee Terry
12	QUENA DEE AIR	Ashlee Terry
13	SHAKIN' BRAVE	Ashlee Terry
14	BAROOGA BRETT	Brooke Ennis
15	BELLA SHIMA	Brooke Ennis
16	BELT UP BUBBS	Brooke Ennis
17	MIGHTY HAZZA	Brooke Ennis
18	MY MATE LENNY	Brooke Ennis
19	PANDAKEN	Brooke Ennis
20	ROCKSTAR STRATUS	Brooke Ennis
21	STERLING ARCHER	Brooke Ennis
22	TRITT TRITT	Brooke Ennis
23	TRUMPSTA	Brooke Ennis
24	YODA	Brooke Ennis
25	ZIPPING ANGUS	Brooke Ennis
26	ZIPPING NANCY	Brooke Ennis
27	BIG BARRA	Kevin Eyles
28	HEARTBREAK HERMY	Kevin Eyles
29	KRAKEN AYE KAY	Kevin Eyles
30	KRAKEN BAM BAM	Kevin Eyles
31	KRAKEN BOLT	Kevin Eyles
32	KRAKEN GRETA	Kevin Eyles
33	KRAKEN PACE	Kevin Eyles
34	MI ROMA	Kevin Eyles
35	WHAT STOPPING YA	Kevin Eyles
Sheet2

Then a VLOOKUP formula like

=Vlookup(A1,$D$1:$E$35,2,0)

so the formula looks up a value equivalent to A1 in the table of D1:E35, and returns the equivalent value in the 2nd column of that table....does that help / make sense ???