VLOOKUP with array formula does not work

[@Marcel]

Hello Excel friends, this is my first thread in this forum and I hope someone can help me. I have this spreadsheet (uploaded) where the array formula does not work. I have tried it again and again and again. I even replaced my keyboard and tried it with different key combinations to put in the curley brackets - no, nothing works. Any assistance on this please? Thank you very much! Best, Marcel

 

[chirayu]

Its because you need to select the relevant cells before you enter an Array e.g. Clear out the Array - J2:M2.

Then click on J2, use the Select keyboard button & use the right key to select till M2

Then start writing the formula "=VLOOKUP($H2,$B$2:$F$13,{2,3,4,5},FALSE)"

Then press Shift Control Enter & the Array should work

 

[rahulshewale1]

Hii @Marcel,


Non array solution
Copy across.
cell J2 =VLOOKUP($H2,$B$2:$F$13,COLUMNS($I$1:J1),FALSE)


Regard
Rahul shewale

 

[@Marcel]

Its because you need to select the relevant cells before you enter an Array e.g. Clear out the Array - J2:M2.

Then click on J2, use the Select keyboard button & use the right key to select till M2

Then start writing the formula "=VLOOKUP($H2,$B$2:$F$13,{2,3,4,5},FALSE)"

Then press Shift Control Enter & the Array should work

Hello Chirayu, thank you for your assistance. The file you returned to me works but look at the formula bar...I have never seen an array formula like this...

 

[@Marcel]

Hii @Marcel,


Non array solution
Copy across.
cell J2 =VLOOKUP($H2,$B$2:$F$13,COLUMNS($I$1:J1),FALSE)


Regard
Rahul shewale

Hi Rahul, thank you for your suggestion - you are a hero!

 

[chirayu]

That's odd, for some reason the commas are changed to \ when you're looking at the file.

 

[Peter Bartholomew]

@Marcel
Going back to your original spreadsheet, you had a column array {2;3;4;5} for the cells you were aiming to return rather than a row {2,3,4,5}. The results will therefore be returned as a column, not a row.
As for the later jpg, the exact format depends upon localisation settings. I have shown English (US and UK) settings but in other regions the ";" is used in place of "," as a parameter and column separator, in which case the row separator becomes "\".