| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 part lookup question
I have a two part question to solve and I hope what is written makes sense
1) in the F column - F1 =12 unless A1 starts with either GM or STW this is to fix a calculation later i would then fill down to cover the F column 2) the following is in G column but I need to add "ignore calculation if D1 is blank" then i would fill down to cover the rest of D =IF(ISNA(VLOOKUP(B20,Sheet2!A:F,4,0)),"",VLOOKUP(B 20,Sheet2!A:F,4,0)) Many thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 part lookup question
Not really sure, but maybe F1: =IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),"",12) G1: = IF(D1="","",IF(ISNA(VLOOKUP(B1,Sheet2!A:F,4,0)),"" ,VLOOKUP(B1,Sheet2!A:F,4,0))) -- __________________________________ HTH Bob "CP" wrote in message ... I have a two part question to solve and I hope what is written makes sense 1) in the F column - F1 =12 unless A1 starts with either GM or STW this is to fix a calculation later i would then fill down to cover the F column 2) the following is in G column but I need to add "ignore calculation if D1 is blank" then i would fill down to cover the rest of D =IF(ISNA(VLOOKUP(B20,Sheet2!A:F,4,0)),"",VLOOKUP(B 20,Sheet2!A:F,4,0)) Many thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 part lookup question
forgot a small part in first question - if A1 does start with GM or STW then
F1 = 6 otherwise it = 12 apologies for that "Bob Phillips" wrote: Not really sure, but maybe F1: =IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),"",12) G1: = IF(D1="","",IF(ISNA(VLOOKUP(B1,Sheet2!A:F,4,0)),"" ,VLOOKUP(B1,Sheet2!A:F,4,0))) -- __________________________________ HTH Bob "CP" wrote in message ... I have a two part question to solve and I hope what is written makes sense 1) in the F column - F1 =12 unless A1 starts with either GM or STW this is to fix a calculation later i would then fill down to cover the F column 2) the following is in G column but I need to add "ignore calculation if D1 is blank" then i would fill down to cover the rest of D =IF(ISNA(VLOOKUP(B20,Sheet2!A:F,4,0)),"",VLOOKUP(B 20,Sheet2!A:F,4,0)) Many thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 part lookup question
=IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),6,12)
If this post helps click Yes --------------- Jacob Skaria "CP" wrote: forgot a small part in first question - if A1 does start with GM or STW then F1 = 6 otherwise it = 12 apologies for that "Bob Phillips" wrote: Not really sure, but maybe F1: =IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),"",12) G1: = IF(D1="","",IF(ISNA(VLOOKUP(B1,Sheet2!A:F,4,0)),"" ,VLOOKUP(B1,Sheet2!A:F,4,0))) -- __________________________________ HTH Bob "CP" wrote in message ... I have a two part question to solve and I hope what is written makes sense 1) in the F column - F1 =12 unless A1 starts with either GM or STW this is to fix a calculation later i would then fill down to cover the F column 2) the following is in G column but I need to add "ignore calculation if D1 is blank" then i would fill down to cover the rest of D =IF(ISNA(VLOOKUP(B20,Sheet2!A:F,4,0)),"",VLOOKUP(B 20,Sheet2!A:F,4,0)) Many thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 part lookup question
Did not work!! I just get 12 all the time.
Cell A1 is a reference number like GM234B, STW21V or L45 What comes after the initial letters can vary not sure if this affects the results "Jacob Skaria" wrote: =IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),6,12) If this post helps click Yes --------------- Jacob Skaria "CP" wrote: forgot a small part in first question - if A1 does start with GM or STW then F1 = 6 otherwise it = 12 apologies for that "Bob Phillips" wrote: Not really sure, but maybe F1: =IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),"",12) G1: = IF(D1="","",IF(ISNA(VLOOKUP(B1,Sheet2!A:F,4,0)),"" ,VLOOKUP(B1,Sheet2!A:F,4,0))) -- __________________________________ HTH Bob "CP" wrote in message ... I have a two part question to solve and I hope what is written makes sense 1) in the F column - F1 =12 unless A1 starts with either GM or STW this is to fix a calculation later i would then fill down to cover the F column 2) the following is in G column but I need to add "ignore calculation if D1 is blank" then i would fill down to cover the rest of D =IF(ISNA(VLOOKUP(B20,Sheet2!A:F,4,0)),"",VLOOKUP(B 20,Sheet2!A:F,4,0)) Many thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 part lookup question
Apologies ignore my last statement - I made a basic error, it works fine many
thanks for the help "CP" wrote: Did not work!! I just get 12 all the time. Cell A1 is a reference number like GM234B, STW21V or L45 What comes after the initial letters can vary not sure if this affects the results "Jacob Skaria" wrote: =IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),6,12) If this post helps click Yes --------------- Jacob Skaria "CP" wrote: forgot a small part in first question - if A1 does start with GM or STW then F1 = 6 otherwise it = 12 apologies for that "Bob Phillips" wrote: Not really sure, but maybe F1: =IF(OR(LEFT(A1,2)="GM",LEFT(A1,3)="STW"),"",12) G1: = IF(D1="","",IF(ISNA(VLOOKUP(B1,Sheet2!A:F,4,0)),"" ,VLOOKUP(B1,Sheet2!A:F,4,0))) -- __________________________________ HTH Bob "CP" wrote in message ... I have a two part question to solve and I hope what is written makes sense 1) in the F column - F1 =12 unless A1 starts with either GM or STW this is to fix a calculation later i would then fill down to cover the F column 2) the following is in G column but I need to add "ignore calculation if D1 is blank" then i would fill down to cover the rest of D =IF(ISNA(VLOOKUP(B20,Sheet2!A:F,4,0)),"",VLOOKUP(B 20,Sheet2!A:F,4,0)) Many thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup Using Part of a Text | Excel Worksheet Functions | |||
| Multi Part Lookup | Excel Discussion (Misc queries) | |||
| 2-part LOOKUP...t'were it possible? | Excel Discussion (Misc queries) | |||
| Part Number Lookup | Excel Worksheet Functions | |||
| Matrix Query Part II - lookup value | Excel Worksheet Functions | |||
Linear Mode
