Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Can someone help me please? Need to know, how would I put into a formula, if
$B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Are you saying you want the related values in columns O, P and Q based on the
VLOOKUP or do you always want specifically cells O3,P3 and Q3. if the first, =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,14,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,15,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,16,0)) if the 2nd =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,whatever column your return value is in,0)&O3&P3&Q3) If you want spaces, add &" "& in between. "Tasha" wrote: Can someone help me please? Need to know, how would I put into a formula, if $B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Tasha,
Try this =IF(AND(B3<"",NOT(ISNA(LOOKUP(A3,Sheet2!B2:B572)) )),Sheet2!O3&Sheet2!O3&Sheet2!Q3,"") Mike "Tasha" wrote: Can someone help me please? Need to know, how would I put into a formula, if $B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Worked like a charm (the first one was what I needed).....Thanks so much
Sean!!!! "Sean Timmons" wrote: Are you saying you want the related values in columns O, P and Q based on the VLOOKUP or do you always want specifically cells O3,P3 and Q3. if the first, =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,14,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,15,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,16,0)) if the 2nd =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,whatever column your return value is in,0)&O3&P3&Q3) If you want spaces, add &" "& in between. "Tasha" wrote: Can someone help me please? Need to know, how would I put into a formula, if $B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Sean, one more thing.... I added a space and a semicolon between the
concatenated records, but for records that don't have anything in O3, P3 and Q3..... I'm getting two semicolons....is there anyway for it to be blank if there is nothing in those fields for the found record? "Sean Timmons" wrote: Are you saying you want the related values in columns O, P and Q based on the VLOOKUP or do you always want specifically cells O3,P3 and Q3. if the first, =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,14,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,15,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,16,0)) if the 2nd =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,whatever column your return value is in,0)&O3&P3&Q3) If you want spaces, add &" "& in between. "Tasha" wrote: Can someone help me please? Need to know, how would I put into a formula, if $B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Thanks!!! I tried your formula Mike, but am not getting anything now.
"Mike H" wrote: Tasha, Try this =IF(AND(B3<"",NOT(ISNA(LOOKUP(A3,Sheet2!B2:B572)) )),Sheet2!O3&Sheet2!O3&Sheet2!Q3,"") Mike "Tasha" wrote: Can someone help me please? Need to know, how would I put into a formula, if $B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Try this
=IF(AND(B3<0,NOT(ISNA(LOOKUP(A3,Sheet2!B2:B572))) ),Sheet2!O3&Sheet2!O3&Sheet2!Q3,"") Mike "sueshe" wrote: Thanks!!! I tried your formula Mike, but am not getting anything now. "Mike H" wrote: Tasha, Try this =IF(AND(B3<"",NOT(ISNA(LOOKUP(A3,Sheet2!B2:B572)) )),Sheet2!O3&Sheet2!O3&Sheet2!Q3,"") Mike "Tasha" wrote: Can someone help me please? Need to know, how would I put into a formula, if $B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF VLOOKUP & CONCATENATE
Well, if all are blank, it would be easier...
=IF($B3=0,"",IF(VLOOKUP($A3,'sheet 2'!$B$2:$U$572,14,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,15,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,16,0)="","",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,14,0)&"; "&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,15,0)&"; "&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,16,0))) "Tasha" wrote: Sean, one more thing.... I added a space and a semicolon between the concatenated records, but for records that don't have anything in O3, P3 and Q3..... I'm getting two semicolons....is there anyway for it to be blank if there is nothing in those fields for the found record? "Sean Timmons" wrote: Are you saying you want the related values in columns O, P and Q based on the VLOOKUP or do you always want specifically cells O3,P3 and Q3. if the first, =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,14,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,15,0)&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,16,0)) if the 2nd =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572,whatever column your return value is in,0)&O3&P3&Q3) If you want spaces, add &" "& in between. "Tasha" wrote: Can someone help me please? Need to know, how would I put into a formula, if $B3=0, then leave cell blank, otherwise, look-up cell A3 on sheet 2 from B2:B572, and if a match is found, concatenate from sheet 2 cells O3, P3 and Q3. This is the formula I have as far as I could go with it, but didn't know how to get the concatenate part in there =IF($B3=0,"",VLOOKUP($A3,'sheet 2'!$B$2:$U$572, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONCATENATE & VLOOKUP | Excel Discussion (Misc queries) | |||
Concatenate a VLOOKUP | Excel Worksheet Functions | |||
Vlookup & Concatenate? | Excel Discussion (Misc queries) | |||
Concatenate and Vlookup | Excel Worksheet Functions | |||
Can you use Concatenate with the If function with vlookup in the i | Excel Worksheet Functions |