DAX = Index and match question

shophoney

Board Regular
Joined
Jun 16, 2014
Messages
219
Hi, I have 2 tables:

SalesGOALSAppend, has LOCATION and SALES GOAL and DATES

EWCHoursBudget, has LOCATION and SALES GOAL and HOURS BUDGET

I would like SalesGOALSAppend to look up in what the EWCHoursBudget are when SALES hit a number.

So if sales at 9100 in SalesGOALSAppend hit $100,000 look up in EWCHoursBudget what the budgeted hours would be.

My sales range is listed in values of about $10,000 so if the sales are between $100,000 and 109,999 then return 400 hours. If it's between 110,000 and 119,999 return the next value which would be 420 hours.

Is this possible?

EWCHoursBudget
locationsaleshours
800060000520
800070000540
800080000590
800090000630
8000100000670
8000110000710
900050000400
900060000450
900070000550
900080000600


SalesGOALSAppend
LOCATIONDATESALES
100001/28/19$37,000
100002/11/19$44,500
100002/25/19$39,700
100003/11/19$62,100
100003/25/19$57,100
100004/08/19$50,100
100004/22/19$63,100
100005/06/19$77,800
100005/20/19$63,400
100006/03/19$34,400
100006/17/19$49,100
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
681
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
A simple VLOOKUP or XLOOKUP should work...
 

Watch MrExcel Video

Forum statistics

Threads
1,095,396
Messages
5,444,233
Members
405,275
Latest member
RefreshSQL

This Week's Hot Topics

Top