# Excel Macro To Lookup a User Entered String, and return data from the field next to it

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?

-

## migrated from stackoverflow.comOct 12 '12 at 16:46

This question came from our site for professional and enthusiast programmers.

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

Vlokup should work for what you are describing.

here are some details as to how to apply this formula to your question.

Formula:

=Vlookup(SearchValue,ColumnSearching:ColumnResultToRight,2,0)

So if you're lookup cell of BCI610 was in sheet1 cell A1 .. and you were looking it up in sheet 2's column A and wanted to return the value to the right of it in column B it would look liek this:

=Vlookup(Sheet1!A1,Sheet2!\$A:\$B,2,0)
-

To add to Gimp's answer, I'd like to point out that the dollar signs are very important if you plan on using the formula in cells below or to the side of the current cell. The dollar signs make the reference to the lookup table absolute, so when you drag the formula into adjacent cells, it looks up the exact same lookup cells. For example:

=VLOOKUP(Sheet1!A1,Sheet2!\$C\$4:\$D\$11,2,FALSE)
-
Thanks for the suggestions, but neither formula worked. The operative word in my question was SOMEWHERE. Somewhere on worksheet B (in an unknown column so Gimp's solution of looking for the string in column A suggests I know the value is in column A, which I don't, without looking, which defeats the purpose of the formula. The macro asks the user for a product number, and the macro is supposed to switch to a different worksheet, find that product number SOMEWHERE, and return the value to the right of it.. –  CJG Oct 11 '12 at 17:42

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
-