IF VLOOKUP & CONCATENATE

[Tasha]

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,

 

[Sean Timmons]

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.

 

[Mike H]

Tasha,

Try this

=IF(AND(B3<>"",NOT(ISNA(LOOKUP(A3,Sheet2!B2:B572)))),Sheet2!O3&Sheet2!O3&Sheet2!Q3,"")

Mike

 

[Tasha]

Worked like a charm (the first one was what I needed).....Thanks so much
Sean!!!!

 

[Tasha]

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?

 

[sueshe]

Thanks!!! I tried your formula Mike, but am not getting anything now.

 

[Mike H]

Try this

=IF(AND(B3<>0,NOT(ISNA(LOOKUP(A3,Sheet2!B2:B572)))),Sheet2!O3&Sheet2!O3&Sheet2!Q3,"")

Mike

 

[Sean Timmons]

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,'sheet2'!$B$2:$U$572,15,0)&"; "&VLOOKUP($A3,'sheet 2'!$B$2:$U$572,16,0)))