• 469K Members
• 4,840 Online
• 567K Conversations

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

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

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

# 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies