Concatenate if? (or similar function)

[michael.reynolds1775]

okay excel wizards,

sheet one titled Main_Personnel column T is certification level (Q, L or T) while the unique ID number is in Column E.

sheet two titled Qual_Cert, column E is the unique ID number and column B is certification level.

how do I create a formula that when the ID numbers match from the two sheets, the value from Qual_Cert B is pasted into Main_Personnel T?

huge thanks in advance if someone can help.

[michael.reynolds1775]

Attached is an example.

I've attempted to use the VLOOKUP to no avail, and the problem may be with my lack of true understanding of the formula.

=VLOOKUP(E2,QUAL_CERT!B2:E2,1,FALSE)

QUAL_CERT EXAMPLE.xlsx

[Glenn Kennedy]

To get VLOOKUP to run from Right to Left, you need to "fool" Exce into doing it. In reality, it's simpler to use INDEX-MATCH.

=INDEX(QUAL_CERT!$B$2:$B$10,MATCH(Main_Personnel!E2,QUAL_CERT!$E$2:$E$10,0))

[michael.reynolds1775]

Glenn,

Thanks for the assistance, but for some strange reason, the formula returns inaccurate information or an error message of #N/A.

[Glenn Kennedy]

It will return #N/A when it fails to find a match (now addressed in this version), but I don't see how it returns inaccurate information, based on your rather limited explanation. Please upload a sheet showing where the problem is.