Delivering Data Based on a table

I have a moderate level of Excel knowledge, but I'm really struggling to come up with a solution to how to create a formula for this. Hopefully someone will be kind enough to help.

In column A I have a set of numbers 0-10 and in column B I have a set of numbers 0-10. I'm trying to find a way to get column C to associate with those numbers. So for example if column A1=5 and B1=4 I'd like C1 to read "$75". Or if column A1=5 and B1=0 I'd like C1 to read "$35". So it's not a basic IF statement. I'm really looking for about 27 possible outcomes and for excel to figure out the conditions of which of those 27 outcomes has been met and provide the corresponding response.

Hopefully this isn't too confusing. Thanks for the help!

joekrebs,

Welcome to the Excel Forum.

Can we see your table?

Please attach your workbook by clicking on the New Reply button, then scroll down and click on the Manage Attachments button.

joekrebs,

The formula in worksheet Carded & XBs Data, cell CY2 (confirmed with CTRL + SHIFT + ENTER, not just the ENTER key), then copied down:

=IF(ISNA(INDEX(Values!$C$2:$C$28,MATCH(1,IF(Values!$A$2:$A$28=$CW2,IF(Values!$B$2:$B$28=$CX2,1)),0))),0,INDEX(Values!$C$2:$C$28,MATCH(1,IF(Values!$A$2:$A$28=$CW2,IF(Values!$B$2:$B$28=$CX2,1)),0)))

Stan,

When I plug this into my excel, I just get a bunch of zeros... Am I doing something wrong? I'm placing it in CY2 as you suggest and then copying down.

EDIT: NEVERMIND... got it working... I'm an idiot...

Ok... One more I need help with in a similar vein to the last one I assume.

What I want to do is as follows:

Have my formula look at column K and from there determine which column the data comes from. So for example, if there is a 4 in column K, I want it to use Column O's number. If Column K said 7, the it would reference Column R.

From there it looks at the "fielding" tab and compares column K against Column A and secondary column against column B. Once it gets a match on both it shows column C.

So the second half of the formula should be fairly identical to the first and I'm sure I can manipulate that to get it to read the columns and such that I want. My question though is how do I get it to look at K then determine what data to pull for the secondary column.

Thanks for tremendously for the help the first time and thank you for the help on this one as well!

joekrebs,

If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

joekrebs,

Have my formula look at column K and from there determine which column the data comes from.

What cell is the first new formula going into?

joekrebs,

The formula in worksheet Carded & XBs Data, cell DK2 (confirmed with CTRL + SHIFT + ENTER, not just the ENTER key), then copied down:



=IF(ISNA(INDEX(Fielding!$C$2:$C$25,MATCH(1,IF(Fielding!$A$2:$A$25=$K2,IF(Fielding!$B$2:$B$25=(INDEX($L2:$R2,MATCH($K2,$L$1:$R$1,0))),1)),0))),0,INDEX(Fielding!$C$2:$C$25,MATCH(1,IF(Fielding!$A$2:$A$25=$K2,IF(Fielding!$B$2:$B$25=(INDEX($L2:$R2,MATCH($K2,$L$1:$R$1,0))),1)),0)))