Concatenate & VLOOKUP
Here is what I got using six VLOOKUP 

=CONCATENATE(VLOOKUP(2,Hm_Roster::A3:E8,3,FALSE),(" "),VLOOKUP(2,Table 1::A3:E8,2,close-match),(" "),VLOOKUP(6,Hm_Roster::A3:E8,2,FALSE),(" "),VLOOKUP(1,Hm_Roster::A3:E8,4,FALSE),(" "),VLOOKUP(5,Table 1::A3:E8,2,close-match),(" "),VLOOKUP(2,Hm_Roster::A3:E8,4,FALSE))






The search is being done across two sheets. And I am using thefirst columns in each table to match against. The first columns contain numbers so that is what I am looking for. Than asking for the return-column to be either 3,2,4 to the right of matched column 







And returns :


  Jone Fuller Silver Sales Person Buchanan Chairman


If you edit your formula you will see that some of the arguments have a drop down selector. These allow you to select the option for that argument.

In the case of Exact-Match. You will see that it is equal to FALSE. so don't worry about it changing.



Make sure you are not using extra brackets where they are not needed. Also I noticed that if I change a cell detail. I had to re-affirmed the formula so that it would pick the change up. I am not sure if thats a bug or not.

(Also note this is the first time I have looked at VLOOKUP )