VLOOKUP with LEFT and FIND function

Hi,

I'm about to use the VLOOKUP and LEFT and FIND function to make a clean result of my roster but unfortunatelly I always end up with error. If you can help me out it would be great.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Originally Posted by AliGW

There are instructions at the top of the page explaining how to attach your sample workbook.

Unfortunately I have no sample workbook and the workbook I use contains confidential data , that's why I've posted the formulas only , I only need to merge somehow those two, but unfortunately I didn't manage.

Briefly what I try to achieve, by selecting the room on the specific day for that person to show automatically in the roster that which days in which room that person works. I have to merge first to assign that room and with the VLOOKUP to put in the room automatically but because I couldn't do without adding the sufix @ plus the room name at the final roster I would like to see that person's name only that's why I have to "cut" everything after.

Sample workbook attached.

firstly your current formula can be shortened
=IFERROR(VLOOKUP("*"&$BI$3,AF3:AF27,1,FALSE),"")

and could be dynamic but you column headers are numbers and not dates

is the below the combining you want
=IFERROR(LEFT(VLOOKUP("*"&$BI$3,AF3:AF27,1,FALSE),FIND("@",VLOOKUP("*"&$BI$3,AF3:AF27,1,FALSE))-1),"")