excel formula to lookup and concatenate horizontal values

user_19150697 0
Viewed 67

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)

Sheet1 DATABASE

Thank you so much!

excel-formula excel
4 Answers

Try below formula-

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

enter image description here

user_55147470

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")
user_108672190

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")

enter image description here

user_63109480

For old version of Excel (older than 2019). First concatenate all address' parts 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")
user_194220660
Related