Hlookup Issue

I'm trying to get back on my sheet the department name for a user id I have in either sheet1's data or sheet 2's data.

But everytime I do a Hlookup it goes N/A when it should display "E&T Aero" as department for the number given in the example.

At the moment it's N/A but if I put in the "TRUE" reference it does the last column of the arrays header.

Any help or a new technique how to get it to lookup appreciated, Thanks

That's not how an HLOOKUP works.

It looks for the search item in the first row of the defined range. If it finds it, it returns the element in the row specified in the same column as the search item.

You could use a User Defined Function (UDF), for example:

Please Login or Register  to view this content.

Called by, for example, =fDeptLookup(B2,D2)

where B2 has the search item and D2 has the sheet name.


Regards

Thanks very much for that, I had it in the wrong place to start with then realised it had to be in a module

See attached.

It's not a standard Excel function ... it's been coded to address your problem.

Regards