Formula Help

Travisallen2007

New Member
Joined
Jan 21, 2018
Messages
26
Office Version
  1. 365
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!
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,955
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What version of Excel are you using?
 

Travisallen2007

New Member
Joined
Jan 21, 2018
Messages
26
Office Version
  1. 365
Found it! Office 365 ProPlus - I have updated in my profile.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,955
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 21, 2018
Messages
26
Office Version
  1. 365

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,955
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 21, 2018
Messages
26
Office Version
  1. 365

ADVERTISEMENT

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.
 

Travisallen2007

New Member
Joined
Jan 21, 2018
Messages
26
Office Version
  1. 365
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,955
Office Version
  1. 365
Platform
  1. Windows
In that case try

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

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top