Index and match - multiple possible match criteria

Hi,

I have an index and match formula written below which pulls the part description from column B on sheet1 by matching the location column in column A on sheet2 and column F on sheet1. This works fine but sometimes the same part may have multiple locations so I'd like it to look in column F, G and H to pull the description. Hope this makes sense, I have put the current formula below which only looks in column F and I have also attached a test spreadsheet

Please Login or Register  to view this content.

Regards

Put on B2 sheet2 :

=IFERROR(INDEX(sheet1!$B$2:$B$6,INT(SMALL(IF(sheet1!$F$2:$H$6=$A2,(ROW(sheet1!$F$2:$H$6)-ROW(sheet1!$F$2)+1)*10^5+COLUMN(sheet1!$F$2:$H$6)-COLUMN(sheet1!$F$2)+1),COUNTIF($A$2:A2,A2))/10^5)),"")

and push F2 buton to open the formula, and oush all together CTRL+SHIFT+ENTER buttton, last step if succed curly brackets will showed up,
after that coped down as necessary