Excel Formula help

Hungrycol

Hi there,

Hope this is posted in the right place!

I meed help with a maahoosive spreadsheet what has a few columns that need comparing but can't tie down the formula. I need a formula for example, if you're a Winner and in Meath you get this prize money, second in Kildare, and so on in the range. The prize money varies depending on county for each placing so the source is biiig when allocating who gets what prize money.

Fpr examply, like this:


Anyone help?

Thanks......

PhoenixParker

I would concatenate the Winner and County together in your prize amounts list.
That will give you a column saying WinnerDublin, SecondDublin etc.
Sort that column alphabetically.

Then in your winners table, do the same thing in a hidden column - concatenate the Placing and the Area

Then use vlookup to look up the prize amounts.

ct5amr2ig1nfhp

Quickest way would probaly to create a Key on your table?
(insert a column to the right of Placing)

PricingKey -- Placing -- Area -- Price

e.g.
WinnerDublin -- Winner -- Dublin -- 85
SecondDublin -- Second -- Dublin -- 75

Define a table for those four columns. For example "PricingTable"

The formula then for your second table to allocate the prizes:

=vlookup(Placing+Area, PricingTable,4,false)

Hope that helps.

syklops

Im not an excel guru but it sounds like you need conditional statements. IF and AND would be the place to start.

Logically you are stating, if, A2(winner) and B2(Dublin), print prize money(500).



So something like: =IF(AND(A2="Winner", B2="Dublin"),"Prize Money amount") ?

Hungrycol

I thought about that PP but am looking for an AND forumla, something like

=if(AND(E:E & F:F)=(A:A & B:B), C:C, 0)

(maybe concatenate is easier! :rolleyes:)

Edit: See more suggestions coming in!

Hungrycol

syklops wrote: »

Im not an excel guru but it sounds like you need conditional statements. IF and AND would be the place to start.

Logically you are stating, if, A2(winner) and B2(Dublin), print prize money(500).



So something like: =IF(AND(A2="Winner", B2="Dublin"),"Prize Money amount") ?

Thanks, syklops, I thought about this to but there's 36 counties and four prize monies for each placing depending on your county so I don't think that an IF statement coud take that many conditions.

ct5amr2ig1nfhp

I'd go the concatenate route tbh.

If you want your worksheet to look "pretty", you can always hide the Key column. Right click on the key column and click Hide.

marcus2000

this should work

{=INDEX($B$2:$D$8,MATCH(G2&H2,$B$2:$B$8&$C$2:$C$8,0),3)}

where $B$2:$D$8 is your first prize fund table.....
G2&H2 are the two columns in your second table that equate to winnerdublin
$B$2:$B$8&$C$2:$C$8 is table one 'placing' and 'area' columns......
3 represents the number of the column that returns the value of the prize.

once typed, you hit ctrl+alt+enter in order to turn the function into an array (and it automatically adds the curly brackets)

syklops

Hungrycol wrote: »

Thanks, syklops, I thought about this to but there's 36 counties and four prize monies for each placing depending on your county so I don't think that an IF statement coud take that many conditions.

It could take that many conditions, but it would be an inelegant solution.

Hungrycol

syklops wrote: »

It could take that many conditions, but it would be an inelegant solution.

Thanks, I went with the concatenate option which was quick and worked well.