Highlighted
New Contributor

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

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
Highlighted

Highlighted

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

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

Highlighted

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

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

See attached file.

Highlighted

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

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