Forum

Notifications
Clear all

VLOOKUP with CHOOSE function keeps delivering #N/A Error

4 Posts
3 Users
0 Reactions
202 Views
(@canetas)
Posts: 15
Eminent Member
Topic starter
 

Hello,

I just can't understand why the vlookup formula with choose keeps me delivering #N/A Error (see attached file)

I need to enter the SKU code in C3 and in B3 pops up the EAN

What's wrong with the formula?!

With Index and Match everything works fine, but i would like to use the vlookup+choose functions.

 

Thanks for the cooperation,

Merry Chritmas,

 

Miguel

 
Posted : 21/12/2019 9:25 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Miguel

You should us a comma (,)instead of a semi colon (;)

Wrong

=VLOOKUP(C3,CHOOSE({1;2},$C$6:$C$18,$B$6:$B$18),2,FALSE)

Correct

=VLOOKUP(C3,CHOOSE({1,2},$C$6:$C$18,$B$6:$B$18),2,FALSE)

Sunny

 
Posted : 21/12/2019 10:35 pm
(@bluesky63)
Posts: 162
Estimable Member
 

Hi Miguel,

Thanks for sharing this method of Vlookup + Choose which can solve a lot of pain for users that still insist to use vlookup, however suffering the pain of each time moving the primary key to the first row,   didn't know this method until you share

This will be another way for those users who like vlookup and do not want to switch to Index&Match or Power Query (merge)

Cheers !

 

Note :  I had propagated VLOOKUP(C3,CHOOSE({1,2},$C$6:$C$18,$B$6:$B$18),2,FALSE)     by changing the underlining range to other field

 
Posted : 22/12/2019 12:05 am
(@canetas)
Posts: 15
Eminent Member
Topic starter
 

Thanks for your approach,

I'm runnig Excel 16 in english, but in Windows 10 in portuguese, and the argumenst in Excel are separated by a semicolon(;) because of the regional settings (when I download a excel file for example from myOnlineTranighub it automaticaly turns comma(,) into semicolon(;) to separate the formulas arguments. But your remark light me some thoughts, perhaps thre's a problem wtihin the Brackts {} and so it is, due to regional settings the symbol to separate arguments inside brakets{} is   {12} and the formula givesme a valid result. Thanks, you give me some clue. Thanks also to Chris Yap.

=VLOOKUP(C3;CHOOSE({12};$C$6:$C$18;$B$6:$B$18);2;FALSE) the formula inWindws 10 in portuguese.

Have a Happy Chritmas and a great year of 2020

 
Posted : 22/12/2019 8:05 pm
Share: