IF cant find selection of multiple keywords return "text"

Hi All!

I currently have a formula that checks a cell for multiple versions of text and responds with a version of text that matches that word, kinda ugly longgggggg formula but it works!

=LOOKUP(J2, {"Analogue Copier Toners","Batteries","Calculators","Computer Storage Media","Facsimile Toners & Ink","Ink Cartridges","Laser Printer Toners","Paper","Photocopier Toners","Ribbons"}, {"Analogue_Copier.jpg","Batteries.jpg","Calculators.jpg","Computer_Storage.jpg","Facsimile_Toners.jpg","Ink_Cartridges.jpg","Laser_Printer_Toners.jpg","Paper.jpg","Photocopier_Toner.jpg","Ribbons.jpg"})

eg. If "Analogue Copier Toners" is found in a cell then return "Analogue_Copier.jpg" ........ and so on which is copied down 3 or 4000 rows.

What i want to do is extend that formula so that if it doesn't find any of these words then it returns "No_Image.jpg"

reason we need this is for our spreadsheet that populates an online store with about 4000 products!


Thanks

Hi, welcome to the forum. Please take a moment to read the forum rules and then amend your thread title and add code tags around your code.

hi Eagle75au, u can use this formula:

=IFERROR(LOOKUP(J2, {"Analogue Copier Toners","Batteries","Calculators","Computer Storage Media","Facsimile Toners & Ink","Ink Cartridges","Laser Printer Toners","Paper","Photocopier Toners","Ribbons"}, {"Analogue_Copier.jpg","Batteries.jpg","Calculators.jpg","Computer_Storage.jpg","Facsimile_Toners.jp g","Ink_Cartridges.jpg","Laser_Printer_Toners.jpg","Paper.jpg","Photocopier_Toner.jpg","Ribbons.jpg"}),"No_Image.jpg")

but i rather u do up a list to VLOOKUP. attached an eg for u. it'll be easier to maintain