Software

Ask something
Discussion Locked

searching in excel

0 helpful
5 replies

by mckay_mark

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a spreadsheet with multiple worksheets. One worksheet contains 2 columns...one with product codes and a second with the product description. What I want to do is on the other worksheets have a formula or a macro that will search for the product code entered by the user and return the corresponding product description based on the product code entered. I have tried the LOOKUP feature but I don't like the fact that if you happen to incorrectly enter the wrong product code it will returnthe next highest or lowest product description. Does anyone have any suggestions? Is there a formula in Excel that will do this? HELP!!

5 replies

by tlg1025

There is a property of the Lookup function that is optional. If you leave it blank it looks for the closest match, if you enter FALSE it returns the #N/A error. To use this proptery enter ",FALSE" after the Row/Column Index number.

by mckay_mark

=LOOKUP(B2,'PRODUCTS & CODES'!A3:A313,'PRODUCTS & CODES'!B3:B313)

This what my formula looks like right now. I tried adding False into the formula but it wouldn't work. Where exactly should I add it?

by tlg1025

I'm sorry, I misunderstood which function you are using. I was thinking you were using the VLOOKUP or HLOOKUP function. I haven't used the LOOKUP function so I can't help you.

by mckay_mark

My fault...the VLOOKUP worked perfectly for me. I looked into it more after reading you response and it works great!!

This discussion is closed to new replies.