VlookUp Multiple Tables to Concatenate

[seanyeap]

Hi Sir/Mdm,
I need advise and tips to fine tune my VlookUp as it only vlookup one table at a time and it returns wrongly if the value I look for is a duplicate.
Basically, I have created 3 tables and each table has a Code to represent each description respectively. I need the vlookup to match and concatenate in a single cell. Please refer to my attached file for better understanding. Any help and advice is greatly appreciated. Thanks in advance.

[Glenn Kennedy]

The main problem here was that the 1 corresponding to Malaysia was stored as text. Now fixed. Is this what you had wanted?

[samba_ravi]

Find the attached file
one more column was created for Digit and used lookup function

[seanyeap]

Hi Glenn,
Thanks for the prompt reply. I need E9 to vlookup the D9 to match the range of =A1:B14. 3 values from the tables must be concatenated in a single cell.
I hope i have cleared the confusion. Thanks a lot.

[Glenn Kennedy]

I was really confused until I saw the edit to your last post.

Try this...

[seanyeap]

Hi Glenn,
I am terribly sorry to have caused the confusion.

[seanyeap]

Hi Glenn,
Now D8 "K43" return correctly from the values of Table 1,2 & 3 as "Kiwi April uk". What if I add 4th digit in D8 as "K435" and "5" will refer to the new table 4?

[Glenn Kennedy]

No it won't. You didn't ask for the inclusion a fourth digit until now. If you want to add more digits, you will FIRST need to define how many, AND you will need a helper column, similar to that suggested by nflsales.

[samba_ravi]

See the attached file

[seanyeap]

Hi nflsales,
It says formula is too long when I added the sixth table. How to simplify the formula so that total of 7 tables will be added? Digit 6 is not allowed to enter. I also attached my file. Thank you.
index.jpg

[Glenn Kennedy]

Hi. Try this...

[seanyeap]

Hi Glenn,
It is amazing, this is what I am looking for. So I can add as many tables as possible. Thanks a lot.

[samba_ravi]

@ Glenn & Seanyeap Pls. Excuse me, if you k53233 instead of k43233 it is giving complete Blank

[Glenn Kennedy]

Did I miss a request by the OP to incorporate something for matches that aren't matches? The way that the request is currently stated, K53233 should (and does) produce a blank.

if Seanyeap wants something else, I'm sure that he'll let us know, and we can take that into account.

Seanyeap: I forgot to add that the formula in Post 11 is an array formula and must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[seanyeap]

Hi Glenn & Nflsales,
Thanks for all your valuable time and effort to get it done, really appreciate. I will try it out to add more tables and see if it works perfectly.