#Name? Error

[Gerhard7777777]

Hi,

I have a column with names in. I want to reflect a certain number connected to the name in the next column.
I have tried numerous formulas which result in a #name? error. This is my formula below. Please help?


VLOOKUP(W6,TRUE,IFS(W6=Bushbuckridge,"1",Thaba Chweu,"2",Mbombela,"3",Nkomazi,"4",Umjindji,"5",Dr JS Moroka,"6",Thembisile,"7",Victor Khanye,"8",Emalahleni,"9",Steve Tshwete,"10",Emakhazeni,"11",Dipaleseng,"12",Govan Mbeki,"13",Msukaligwa,"14",Albert Luthuli,"15",Lekwa,"16",Pixley Ka Seme,"17",Mkhondo,"18",""))

 

[AliGW]

You need to put the inverted commas around the names, not the numbers. Like this:

=VLOOKUP(W6,TRUE,IFS(W6="Bushbuckridge",1,"Thaba Chweu",2, etc.

 

[Gerhard7777777]

Hi Ali

I have tried that option and it still results in a #Name? error.

 

[AliGW]

Which version of Excel are you using> Does it support the IFS function?

 

[Special-K99]

I think you still have to put W6= for each element of the IFS command.
See the example here

https://support.office.com/en-gb/ar...4a39bd951d45?ui=en-US&rs=en-GB&ad=GB&fromAR=1

e.g.
IFS(W6=1,"abc",W6=2,"bbb" etc )

 

[AliGW]

Good point, except it will be this:

=IFS(W6="abc",1,W6="bbb",2, etc.

 

[peterhinton]

I've never saw or used IFS before, I'm only on 2010, what version was this introduced guys ?

 

[AliGW]

Excel 2016 - see here: https://support.office.com/en-gb/ar...4a39bd951d45?ui=en-US&rs=en-GB&ad=GB&fromAR=1

 

[RoryA]

Also note that using TRUE for the second argument of your VLOOKUP won't work.