1

In Numbers, I'm trying to enhance a Concatenated formula of multiple VLOOKUP results. I'm currently producing a result of three concatenated answers (each a specific VLOOKUP formula). I would like to double that to six VLOOKUP formulas...Though duplicating the existing formulas, I keep getting errors.

Here is my formula currently:

=CONCATENATE (VLOOKUP($A$3,Hm_Roster :: $A$2:$L$201,4, FALSE),(" '"),(" "),(VLOOKUP($A$3,Hm_Roster :: $A$2:$L$201,5,FALSE)), (""), (", "),(VLOOKUP($A$3,Hm_Roster :: $A$2:$L$201,6,FALSE)))

You likely deducted, "Hm_Roster" is another page inside the document from which the data is being concatenated. Also, likely obvious: in the document I see "exact match" but when I paste the formulas it reads "FALSE"

  • Can you please post your formulas as is now – markhunte Sep 29 '14 at 16:59
  • =CONCATENATE (VLOOKUP($A$3,Hm_Roster :: $A$2:$L$201,4, FALSE),(" '"),(" "),(VLOOKUP($A$3,Hm_Roster :: $A$2:$L$201,5,FALSE)), (""), (", "),(VLOOKUP($A$3,Hm_Roster :: $A$2:$L$201,6,FALSE))) – JD34 Sep 29 '14 at 17:04
  • You likely deducted, "Hm_Roster" is another page inside the document from which the data is being concatenated. Also, likely obvious: in the document I see "exact match" but when I paste the formulas it reads "FALSE" – JD34 Sep 29 '14 at 17:12
  • 1
    I've attempted to include the information from your comments in your original question, but feel free to edit further. Also, in the future please add such information directly to your question as comments are transient. – grg Sep 29 '14 at 17:31
  • As grgarside says. for future ref my answer below also shows you how to add information to your question to illustrate what you have and what you want. The less info you provide will lead to misunderstanding of your question. Which my answer may be also – markhunte Sep 29 '14 at 18:12
1

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

enter image description here


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

enter image description here


enter image description here

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.

enter image description here

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 )

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .