Formula Help

[Travisallen2007]

I have two tables and need Table A (Which is changeable) To source its data off table B. I am not sure how to set this up and any help would be greatly appreciated.

Table A
A23:A31 is changable Word corresponding to cash amounts. Drop down based on table at E53:F60 (Also now called table B)
B23:B31 Is where the user types in quantities of the dollars they have
C23:C31 is where i need it to calculate to dollar value from Table B associated with the choice they selected in A23:A31 multiplied by B23:B31

Table B:
E53:E60 is the titles of the drop downs that will be selectable for A23:A31 (With one exception listed below)
F53:F60 is the dollar amounts that need to be used based on the selection chosen in table A A23:A31 and later used to multiply showing total dollar amount in cells C23:C31

The only exception to this is for pennies, nickels, dimes and Qtrs that never change. Pennies are always .50, Nickels always 2, dimes always 5, and quarters always 10.

Thanks again for any help!! Really appreciate it!

 

[Fluff]

Hi & welcome to MrExcel.
What version of Excel are you using?

 

[Travisallen2007]

Found it! Office 365 ProPlus - I have updated in my profile.

 

[Fluff]

Ok, thanks for that, can you add the values for Dimes,Quarters etc to your table in E53?
If so you could use
=B23*IFERROR(VLOOKUP(A23,$E$53:$F$60,2,0),0)

 

[Travisallen2007]

This works well - was trying to avoid adding lines due to space but i will be able to scrunch this in!

Thanks for the help!

 

[Fluff]

You would prefer not to include them, you could use
=B3*IFERROR(VLOOKUP(A23,$E$53:$F$60,2,0),LOOKUP(A23,{"Dimes","Nickels","Pennies","Quarters"},{5,2,0.5,10}))

 

[Travisallen2007]

Ac

This works well - was trying to avoid adding lines due to space but i will be able to scrunch this in!

Thanks for the help!

Actually just noticed the compatibility error - This will need to function on office 2007 as well. It is looking like that fix wont be compatible.

 

[Fluff]

Both those formulae should work with 2007.

 

[Travisallen2007]

Both those formulae should work with 2007.

Im so so sorry for conflicting info - doing to many things at once today.

It needs to work with 97-2003 versions. Apologies again. Thank you so much for all your help!

 

[Fluff]

In that case try

=B23*IF(ISERROR(VLOOKUP(A23,$E$53:$F$60,2,0),0,VLOOKUP(A23,$E$53:$F$60,2,0))