Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

On worksheet A, a user is prompted to enter a product number, such as BCI610. On worksheet B somewhere, that value exists.

I want to lookup/find that value, and then return the data in the cell that is right next to it one column to the right, by copying that data, and pasting it somewhere in worksheet A.

If I enter BCI610, it should return the value M332651, because that is the number in the cell immediately to the right of BCI610.

I tried VLookup and HLookup, but to no avail... Any suggestions?


migration rejected from Feb 21 at 12:44

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by fixer1234, DragonLord, mdpc, DavidPostill, Nifle Feb 21 at 12:44

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

How is your data set up? Is there a particular reason that VLOOKUP/HLOOKUP failed? If it is because the lookup is a bit more complex, you could try an INDEX/MATCH formula. – RocketDonkey Oct 11 '12 at 1:44
-1, missing info from comments below - the looked-up value can be in any column in worksheet B and the value from 1 cell right to this unknown cell should be returned – Aprillion Oct 13 '12 at 14:02
I'm voting to close this question because it is unclear and confusing. The ambiguous "somewhere" is discussed in a comment on an answer and appears to mean that the location of the lookup values is unknown. This changes the required nature of the answer. – fixer1234 Feb 20 at 16:23

1 Answer 1

if there is only one match in worksheet B and you can narrow down the range where to look, you can use a formula like this to find the value user enters in cell A1 of the current worksheet:

=SUMPRODUCT(--(B!A1:Z1000=A1), ROW(B!A1:Z1000) * 1000 + COLUMN(B!A1:Z1000))

and return the value of the cell next to it like this:

=INDEX(B!B1:AA1000, INT(B1/1000), MOD(B1;1000))

for a more general situation and/or better performance you can use some code like this in your macro:

search_for = Worksheets("A").Range("A1").Value2
result = Worksheets("B").UsedRange.Find(search_for).Offset(0, 1).Value2
Worksheets("A").Range("B1").Value2 = result