VLOOKUP & IF used with > or <

[Dale G]

Hi,
I’m trying to return an employee’s name in column I using VLOOKUP from the
value in column A.
The value is a number that the employee is assigned as a piece of work.
The numbers are 200-899 and these employees work Monday – Friday, 900-949
Work Monday – Thursday, & 950-999 work Tuesday – Friday.
On Friday 900-949 is done by an employee who will work Friday, Saturday,
Sunday, & on Monday the same employee will be assigned a 950-999 piece of
work. (Saturday & Sunday has a different set up)
So far I’ve been using 3 formulas to accomplish this LOOKUP and they are;
This is for all the numbers Monday-Friday
=VLOOKUP(A3,Feeder!$A$2:$C$118,3,0)
This is for 900-949 with Friday off (which will use the same lookup as above
on Monday-Thursday)

=IF($A$1="Friday",VLOOKUP(A4,Feeder!$H$3:$I$20,2,0),VLOOKUP(A4,Feeder!$A$2:$C$118,3,0))
This is for 950-999 with Monday off (which will use the same lookup as above
on Tuesday-Friday
=IF($A$1="Monday",VLOOKUP(A5,Feeder!$H$22:$I$39,2,0),VLOOKUP(A5,Feeder!$A$2:$C$118,3,0))
Is it possible to have one formula that would do the job of these three?
I’ve been trying to have something like this but I can’t get it to work
=IF($A$1="Friday",$A7<950,VLOOKUP(A7,Feeder!$H$3:$I$20,2,0),VLOOKUP(A5,Feeder!$A$2:$C$118,3,0))
Any help is appreciated.

 

[T. Valko]

=IF($A$1="Friday",$A7<950,VLOOKUP(A7,Feeder!$H$3:$I$20,2,0),VLOOKUP(A5,Feeder!$A$2:$C$118,3,0))

Whether this works or not I don't know but here's how you can write that
formula:

=IF(AND($A$1="Friday",$A7<950),VLOOKUP(A7,Feeder!$H$3:$I$20,2,0),VLOOKUP(A5,Feeder!$A$2:$C$118,3,0))

 

[Dale G]

Thank you,
That seems to work well, but I need to be able to use a Monday lookup with
that. Would it be possible to combine the two below?
=IF(AND($A$1="Monday",$A8>=950),VLOOKUP(A8,Feeder!$H$3:$I$39,2,0),VLOOKUP(A8,Feeder!$A$2:$C$118,3,0)
=IF(AND($A$1="Friday",$A8<950),VLOOKUP(A8,Feeder!$H$3:$I$39,2,0),VLOOKUP(A8,Feeder!$A$2:$C$118,3,0))

 

[T. Valko]

Not sure how you mean to combine them.

Do you mean something like this:

If A1 = Mon or Fri and A8>=950, this range if true, If A1 = Mon or Fri and
A8<950, this range if true, this range if false

 

[Dale G]

I think it may look like this somewhat?
If A1 = Friday and A8<950, “True†Range Feeder H3:I39, IF “False†Range
Feeder A2:C188
also
If A1= Monday and A8>=950, "True" Range Feeder H3:I39, IF "False" Rage
Feeder A2:C188

 

[T. Valko]

Ok, I'm still not sure I understand but maybe this:

=IF(OR(AND($A$1="Monday",$A8>=950),AND($A$1="Friday",$A8<950)),VLOOKUP(A8,Feeder!$H$3:$I$39,2,0),VLOOKUP(A8,Feeder!$A$2:$C$118,3,0))

 

[Dale G]

Thank you again, it works.
Here’s what I ended up with.
=IF(OR(AND($A$1="Monday",$A3>=950),AND($A$1="Friday",$A3<950)),VLOOKUP(A3,Feeder!$A$2:$C$118,3,0),VLOOKUP(A3,Feeder!$A$2:$B$118,2,0))
I had to increase the (Range) of the first lookup. I also changed the range
to A,B,C 2:118
I always have a hard time explaining the purpose of my lookup tables.
It would probably be easier if you were able to see what I’m working on.
Never the less it works and thank you for your help.

 

[T. Valko]

You're welcome. Thanks for the feedback!