Multiple VLOOKUPS with specific text outputs in IF statements

[Helloitsme]

I have been looking everywhere for a solution but have had no luck so far, hopefully one of you can help me with this.

I want to compare the text in E2 with a list of text in I, J, and K, and
if the VLOOKUP is succesful in I, the formula should input the text "Nybyg",
if unsuccesful it should check J, if succesful here it should input the text "Fiber Factory",
if unsuccesful it should check K, if succesful here it should input the text "BTO"
if unsuccesful in all of the above, it should input the text "NS".

The following equation is as far as my capabilities get me, which results in "#N/A" being output in the formula field, if the "Nybyg" VLOOKUP is unsuccesful:

=IF(VLOOKUP(E2;I:I;1;FALSE)=E2;"Nybyg";
IF(VLOOKUP(E2;J:J;1;FALSE)=E2;"Fiber Factory";
IF(VLOOKUP(E2;K:K;1;FALSE)=E2;"BTO";
NS)))

Thank you in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(ISNUMBER(XMATCH(E2,I2:I1000,0)),"Nybyg",IF(ISNUMBER(XMATCH(E2,J2:J1000,0)),"Fiber Factory",IF(ISNUMBER(XMATCH(E2,K2:K1000,0)),"BTO","NS")))

 

[etaf]

you seem to be just checking if E2 exists in the list - if so rather than vlookup, try a countif()

=IF(COUNTIF(I:I,E2)>0;"Nybyg";
IF(COUNTIF(J:J,E2)>0;"Fiber Factory";
IF(COUNTIF(K:K;E2)>0;"BTO";
NS)))

 

[Helloitsme]

Hi & welcome to MrExcel.
How about

=IF(ISNUMBER(XMATCH(E2,I2:I1000,0)),"Nybyg",IF(ISNUMBER(XMATCH(E2,J2:J1000,0)),"Fiber Factory",IF(ISNUMBER(XMATCH(E2,K2:K1000,0)),"BTO","NS")))

Worked like an absolute charm, after having my head wrapped around this for hours on end, you have my appreciation! Thank you so much Fluff

 

[Helloitsme]

you seem to be just checking if E2 exists in the list - if so rather than vlookup, try a countif()

=IF(COUNTIF(I:I,E2)>0;"Nybyg";
IF(COUNTIF(J:J,E2)>0;"Fiber Factory";
IF(COUNTIF(K:K;E2)>0;"BTO";
NS)))

This is also a viable solution, however #NAME? error is returned in the non-matching fields, not sure why. This formula does, however, continue with checking for more than just the first line! Thank you very much!

 

[Fluff]

With etaf's solution you need to put the NS inside quotes "NS", that will get rid of the error.