Whatsthe formula which retrieves a # from a multiplication table
I have a table that is similar to a multiplication table.
I would like to write a formula in a single cell (example A3) that retrieves data from a table by matching values typed in cells A1 and A2 (Example1: A1 has the number 5 typed in and A2 has the number 10 typed in). A3 should result with a 50.) (Example2: A1 has the number 6 typed in and A2 has the number 3 typed in). A3 should result with a 18.) |
Whatsthe formula which retrieves a # from a multiplication table
Hi DS,
It looks as if a3 could contain the formula: =a1*a2 Hope that helps ? Anthony "DS" wrote: I have a table that is similar to a multiplication table. I would like to write a formula in a single cell (example A3) that retrieves data from a table by matching values typed in cells A1 and A2 (Example1: A1 has the number 5 typed in and A2 has the number 10 typed in). A3 should result with a 50.) (Example2: A1 has the number 6 typed in and A2 has the number 3 typed in). A3 should result with a 18.) |
Whatsthe formula which retrieves a # from a multiplication table
How do I write a formula to match
B2 (Column Search/Vlookup) and B3 (Row Search/Hlookup) AND return a value from a table A5:D9 (assigned codes per dept and acct) ? Example B2 - typed in "10200" B3 type in "70100" should return in B4 "s" Example B2 - typed in "10300" B3 type in "70100" should return in B4 "a" Example B2 - typed in "10200" B3 type in "70101" should return in B4 "e" Below I have done a copy and paste of my excel file: Row 1 Column B Row 2 _10200_ (Type in Dept #) Row 3 _70100_ (Type in Account #) Row 4 _______ (Formula should return an Alpha Code from table below) Column A Column B Column C Column D Row 5 Dept # Dept # Dept # Row 6 10100 10200 10300 Row 7 Acct# 70000 a c h Row 8 Acct# 70100 d s a Row 9 Acct# 70101 b e g "DS" wrote: I have a table that is similar to a multiplication table. I would like to write a formula in a single cell (example A3) that retrieves data from a table by matching values typed in cells A1 and A2 (Example1: A1 has the number 5 typed in and A2 has the number 10 typed in). A3 should result with a 50.) (Example2: A1 has the number 6 typed in and A2 has the number 3 typed in). A3 should result with a 18.) |
Whatsthe formula which retrieves a # from a multiplication tab
Hi,
In b4: =OFFSET(B7,MATCH(C2,A7:A9)-1,MATCH(B2,B6:D6)-1) should work fine. Hope that is helpful Anthony "DS" wrote: How do I write a formula to match B2 (Column Search/Vlookup) and B3 (Row Search/Hlookup) AND return a value from a table A5:D9 (assigned codes per dept and acct) ? Example B2 - typed in "10200" B3 type in "70100" should return in B4 "s" Example B2 - typed in "10300" B3 type in "70100" should return in B4 "a" Example B2 - typed in "10200" B3 type in "70101" should return in B4 "e" Below I have done a copy and paste of my excel file: Row 1 Column B Row 2 _10200_ (Type in Dept #) Row 3 _70100_ (Type in Account #) Row 4 _______ (Formula should return an Alpha Code from table below) Column A Column B Column C Column D Row 5 Dept # Dept # Dept # Row 6 10100 10200 10300 Row 7 Acct# 70000 a c h Row 8 Acct# 70100 d s a Row 9 Acct# 70101 b e g "DS" wrote: I have a table that is similar to a multiplication table. I would like to write a formula in a single cell (example A3) that retrieves data from a table by matching values typed in cells A1 and A2 (Example1: A1 has the number 5 typed in and A2 has the number 10 typed in). A3 should result with a 50.) (Example2: A1 has the number 6 typed in and A2 has the number 3 typed in). A3 should result with a 18.) |
Whatsthe formula which retrieves a # from a multiplication tab
DS,
Thank you for your feedback. Thought it would be worth mentioning as well that the Dept #'s and Account #'s look to be sorted into ascending order. Just in case that would not always hold, the match type can be changed to 0 from 1 (default is 1 when it is not otherwise not specified). =OFFSET(B7,MATCH(C2,A7:A9,0)-1,MATCH(B2,B6:D6,0)-1) Anthony "Anthony D" wrote: Hi, In b4: =OFFSET(B7,MATCH(C2,A7:A9)-1,MATCH(B2,B6:D6)-1) should work fine. Hope that is helpful Anthony "DS" wrote: How do I write a formula to match B2 (Column Search/Vlookup) and B3 (Row Search/Hlookup) AND return a value from a table A5:D9 (assigned codes per dept and acct) ? Example B2 - typed in "10200" B3 type in "70100" should return in B4 "s" Example B2 - typed in "10300" B3 type in "70100" should return in B4 "a" Example B2 - typed in "10200" B3 type in "70101" should return in B4 "e" Below I have done a copy and paste of my excel file: Row 1 Column B Row 2 _10200_ (Type in Dept #) Row 3 _70100_ (Type in Account #) Row 4 _______ (Formula should return an Alpha Code from table below) Column A Column B Column C Column D Row 5 Dept # Dept # Dept # Row 6 10100 10200 10300 Row 7 Acct# 70000 a c h Row 8 Acct# 70100 d s a Row 9 Acct# 70101 b e g "DS" wrote: I have a table that is similar to a multiplication table. I would like to write a formula in a single cell (example A3) that retrieves data from a table by matching values typed in cells A1 and A2 (Example1: A1 has the number 5 typed in and A2 has the number 10 typed in). A3 should result with a 50.) (Example2: A1 has the number 6 typed in and A2 has the number 3 typed in). A3 should result with a 18.) |
All times are GMT +1. The time now is 08:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com