| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
![Dale G[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
