Take the 2-minute tour ×
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?

share|improve this question

migrated from stackoverflow.com Oct 12 '12 at 16:46

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

1  
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

3 Answers 3

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
share|improve this answer

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)
share|improve this answer
    
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

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.