HLOOKUP finding rows above lookup value.

[GPMan]

Hello Excel Forum. I have a user form I want to let users find and edit items from the workbook.

There is a text field(codeEntry.text) where the users can type or select a product code. Then I need some information to be shown after they clicked on Select Button(cmdSelect1).
Here is the problem. There are 2 ranges of information I couldn't get from normal HLOOKUP method.
The data in the text field that I use a lookup value is in row 4 but the data I want to look for are in row 2 and 3. And there is nothing I can do about the layout of the workbook. My boss does not allow any changes.

The worksheet name is "PPAIs", same as workbook itself.

Here's a pic.
Crop1.png

The text box used to enter a search value is "codeEntry".
The text box where a value from row 2 will be shown is "showUnit".
The text box where a value from row 3 will be shown is "showPrice".
The select button is "cmdSelect1". And the values should be shown after clicked.

What can I use to get a value from these upper rows?

Thank you in advance. Any help is appreciated.

By the way, this is user form.
Crop2.png

[BadlySpelledBuoy]

Rather than use HLOOKUP, have a look at using INDEX/MATCH to return the values as it will allow you to look either side of the reference value.

It works like this:

Please Login or Register  to view this content.

And would look something like this:

Please Login or Register  to view this content.

The above formula will search in row 3 for the value in cell A1 and return the value from the corresponding column in row 2.

Hopefully that helps a little.

If not, attach your workbook and we'll help from there.

BSB