excel formula to lookup and concatenate horizontal values-CodePudding

Pick license plate in Sheet1 and look up on DATABASE Then Address column should return concatenated values from location to zip (like in the red highlight)

Thank you so much!

CodePudding user response：

Try below formula-

=TEXTJOIN(" ",TRUE,IFERROR(FILTER(Database!$B$2:$F$5,Database!$A$2:$A$5=A2),"No Data"))

CodePudding user response：

Try this

There are two sheets Sheet 1 includes license plate and address, and second sheet called DATABASE includes license plate, location, street, city, state, zip

For Europe.

=IFERROR(VLOOKUP(A2;DATABASE!$A:$F;2;FALSE)&" "&VLOOKUP(A2;DATABASE!$A:$F;3;FALSE)&" "&VLOOKUP(A2;DATABASE!$A:$F;4;FALSE)&" "&VLOOKUP(A2;DATABASE!$A:$F;5;FALSE)&" "&VLOOKUP(A2;DATABASE!$A:$F;6;FALSE);"No Data Found")

For US.

=IFERROR(VLOOKUP(A2,DATABASE!$A:$F,2,FALSE)&" "&VLOOKUP(A2,DATABASE!$A:$F,3,FALSE)&" "&VLOOKUP(A2,DATABASE!$A:$F,4,FALSE)&" "&VLOOKUP(A2,DATABASE!$A:$F,5,FALSE)&" "&VLOOKUP(A2,DATABASE!$A:$F,6,FALSE),"No Data Found")

CodePudding user response：

Just tested:

=IFERROR(INDEX(Database!B:B&" "&Database!C:C&" "&Database!D:D&" "&Database!E:E&" "&Database!F:F,MATCH(Sheet1!A2,Database!A:A,0)),"No Data")

CodePudding user response：

For old version of Excel (older than 2019). First concatenate all addresses in second sheet: In "G2" of second sheet enter:

=CONCATENATE(B2&" "&C2&" "&D2&" "&E2&" "&F2)

Then in "B2" of first sheet enter:

=IFERROR(INDEX(Sheet2!$G$2:$G$6;MATCH(A2;Sheet2!$A$2:$A$6;0);"No Data")