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.)

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.)

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.)

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.)

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.)