Lookup formula

  • Thread starter Thread starter Denise B
  • Start date Start date
D

Denise B

I have a table that displays the days of the week and work
load with their corresponding staffing requirements.

Mon Tues Wed Thur Fri Sat Sun

100 5 6 6 7 8 9 9

200 6 7 7 8 8 9 9

300 7 8 8 9 8 9 9


So on monday if we have 100 customers I need 5 staff
members.

My question is that I need a formula to look at 140
customers on a Monday and tell me that I need 5 staff
members because it is between 100-200.

I know this crazy to ask this on a newsgroup but I thought
I would give it a try.

Thank you,

Denise
 
Hi Denise
try the following formula (assumption your weekdays are in row 1 and
your customer numbers in column A and your data starts in B2 for monday
and 100 customers):
=INDEX($A$2:$H$5,MATCH($I$1,$A$2:$A$5,1),MATCH($J$1,$A$1:$H$1,0)
where I1 stores your customers number and J1 your weekday

HTH
Frank
 
Denise
The following assumes days of week in B1:H1, 100-300 in A2:A4, and the table of number in B2:H4. The day is entered in A8, and the customer quantity in B8

=INDEX($B$2:$H$4,MATCH(B8,$A$2:$A$4),MATCH(A8,$B$1:$H$1)

Keep in mind that if you have 199 customers on Monday this will return a 5. Did you want to incorporate some rounding into this function

A good site to check out on this subject is
http://www.cpearson.com/excel/lookups.htm#ClosestMatc

Good Luck
Mark Graesse
(e-mail address removed)

----- Denise B wrote: ----

I have a table that displays the days of the week and work
load with their corresponding staffing requirements

Mon Tues Wed Thur Fri Sat Su

100 5 6 6 7 8 9

200 6 7 7 8 8 9

300 7 8 8 9 8 9


So on monday if we have 100 customers I need 5 staff
members

My question is that I need a formula to look at 140
customers on a Monday and tell me that I need 5 staff
members because it is between 100-200.

I know this crazy to ask this on a newsgroup but I thought
I would give it a try

Thank you

Denis
 
Back
Top