Need formula to return specific data from a predefined table - I think.

Copper Contributor

I have created a table (only on paper so far) in which Column A contains 6 Revenue Ranges. Row 1 contains 5 Machine Quantity Ranges. The "Data" in the table is simply the number of preventive maintenance visits I have established based on the defined ranges. For example, a revenue range of $4001 - $8k and a machine quantity of 1 is 2 visits. There is no formula in place to create the data...I just entered the values manually. Easy.

 

I have a master spreadsheet showing all of my active accounts. Column M shows Annual Average Revenue per Machine and is labeled AVG/MACH. Column K shows the total number of machines  at a particular location and is labeled TOTAL UNITS. In Column P, I would like to enter a formula that compares the values in Columns M and K to the corresponding ranges in the table and returns the corresponding number (the number of pre-established visits I defined in the table).

 

This seems like it would be relatively easy to do, but I have no idea where to start. Please Help!!!

4 Replies
Please attach your sample Excel file.

I didn't have a sample Excel Workbook when I first posted...I do now.

The row and column values in sheet "Draft Frequency Table" need to be numbers.

See attached file.

 

Hello Erik,

In the attached file, the formula in P2 is: 

=INDEX(VisitTable,
MATCH(M2,RevenueRange,1),
MATCH(K2,MachineQtyRange,1))

Note that I modified the Row and Column Labels to suit the formula for an approximate match.

Cheers!

Twifoo