vlookup and concotanate formula returning a reference error

Hello, it’s my first post here but I’ve looked through the other threads concerning this issue and they don’t seem to solve my issue. First, I am trying to create a codebook that dynamically populates itself. The codebook contains a concatenated code, based on pieces of text from a transcript, that form a code. Second, I am trying to use the concatenated code as the basis for a vlookup function which will automatically populate a cell if two concatenated codes are the same and return a text value (code memo). If they are not the same I would like to add the text “PLEASE INSERT A CODE MEMO”.

I have created the code book by returning the value from another sheet ‘transcript’. I have inserted this formula from columns B to F in the codebook sheet.
e.g =transcripts!H2

I have then concatenated all these values together using the formula
=CONCATENATE(B2&":"&C2&":"&D2&":"&E2&":"&F2)

In the transcript sheet I have entered another formula that returns that concatenated value. I then attempt a vlookup from the concatenated value. However, the vlookup returns a REF error. I’m not too sure how to solve this ref error so any help would be much appreciated.
Lastly, because other people will work on this workbook is it possible to lock the formulas apart from the ones that are not the same and return the text “PLEASE INSERT A CODE MEMO”?
Attached is the workbook.

Regards,

Mark

Hi,
Try this formula:
=VLOOKUP(M2,'Code book'!$A:$G,7,0)

Hello, thanks for responding so quickly.

The vlookup formula is

=VLOOKUP(M2,'Code book'!A:G,'Code book'!A1,2)

There are two sheets in the workbook. The first 'transcript' sheet contains the vlookup function and the codebook sheet contains the value I want to lookup

Thank you very much duanzhuanming, that formula works great. Why does it work with a 0 instead of 2 in the reference value?

Your formula is not correct...
=VLOOKUP(M2,'Code book'!A:G,'Code book'!A1,2)
In your sheet: 'Code book'!A1 is not a number for column position in which the formula will return value.
'Code book'!A1 =Concatenated code
Formula Syntax is false!

Originally Posted by okeefe87

Thank you very much duanzhuanming, that formula works great. Why does it work with a 0 instead of 2 in the reference value?

You can type False or True
False =0 --> VLOOKUP will find only an exact match
True =1

Thanks for the help and the tips. They are much appreciated.

Best regards,

Mark