|
VLOOKUP & IF used with or <
Hi,
Im trying to return an employees 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 Ive 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? Ive been trying to have something like this but I cant 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. |
VLOOKUP & IF used with or <
=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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
VLOOKUP & IF used with or <
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" wrote: =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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
VLOOKUP & IF used with or <
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 -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: =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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
VLOOKUP & IF used with or <
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" wrote: 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 -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: =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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
VLOOKUP & IF used with or <
Ok, I'm still not sure I understand but maybe this:
=IF(OR(AND($A$1="Monday",$A8=950),AND($A$1="Frida y",$A8<950)),VLOOKUP(A8,Feeder!$H$3:$I$39,2,0),VLO OKUP(A8,Feeder!$A$2:$C$118,3,0)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: =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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
VLOOKUP & IF used with or <
Thank you again, it works.
Heres what I ended up with. =IF(OR(AND($A$1="Monday",$A3=950),AND($A$1="Frida y",$A3<950)),VLOOKUP(A3,Feeder!$A$2:$C$118,3,0),VL OOKUP(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 Im working on. Never the less it works and thank you for your help. "T. Valko" wrote: Ok, I'm still not sure I understand but maybe this: =IF(OR(AND($A$1="Monday",$A8=950),AND($A$1="Frida y",$A8<950)),VLOOKUP(A8,Feeder!$H$3:$I$39,2,0),VLO OKUP(A8,Feeder!$A$2:$C$118,3,0)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: =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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
VLOOKUP & IF used with or <
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Dale G" wrote in message ... Thank you again, it works. Here's what I ended up with. =IF(OR(AND($A$1="Monday",$A3=950),AND($A$1="Frida y",$A3<950)),VLOOKUP(A3,Feeder!$A$2:$C$118,3,0),VL OOKUP(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" wrote: Ok, I'm still not sure I understand but maybe this: =IF(OR(AND($A$1="Monday",$A8=950),AND($A$1="Frida y",$A8<950)),VLOOKUP(A8,Feeder!$H$3:$I$39,2,0),VLO OKUP(A8,Feeder!$A$2:$C$118,3,0)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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" wrote: =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)) -- Biff Microsoft Excel MVP "Dale G" wrote in message ... 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. |
| All times are GMT +1. The time now is 08:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com