Excel formula to return a value from a table (account number and month)

[danielv1000]

Hi community,

I would appreciate assistance returning a value based on two criteria (row and column).

I'm looking to return a value in the 'Financial Data tab' from the table in the 'Report 'tab.

For example, in cell F7, the value returned should be 3,322,922.11. (I populated April using a xlookup formula). 

Really appreciate the help :folded_hands: (I can't seem to attach the example file)

Thanks,
Daniel

[#2 · Accepted solution]

@danielv1000 

To return a value based on two criteria (row and column) in Excel, you can use the INDEX and MATCH functions together. Here is how you can do it:

Assuming your table in the 'Report' tab starts from cell A1 and extends to cell H7, and your criteria (account number and month) are in cells E7 and F7 respectively, you can use the following formula in cell G7 of the 'Financial Data' tab:

=INDEX(Report!$B$2:$H$7, MATCH($E7, Report!$A$2:$A$7, 0), MATCH($F7, Report!$B$1:$H$1, 0))

This formula will return the value from the table in the 'Report' tab based on the account number (criteria in cell E7) and the month (criteria in cell F7).

Explanation of the formula:

• INDEX(array, row_num, [column_num]): Returns the value of a cell in a table based on the row and column numbers.
• MATCH(lookup_value, lookup_array, [match_type]): Returns the relative position of an item in a range that matches a specified value.
• $ signs are used to make the references absolute, so they won't change when you copy the formula to other cells.

Make sure to adjust the ranges in the formula to match the actual range of your table in the 'Report' tab. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.