Excel: search for string in Colum 1 and match to value in column 2

Question

I am trying to write a Visual Basic code for an Excel worksheet.

I have data in Sheet 2 and would like to retrieve values from Sheet 2 and return them to a cell in Sheet 1.

First I need to search in Sheet 2, Column A for the string "TOTAL OR". Once that value is found, I then need to search Sheet 2, Column B for the value that matches "TOTAL OR" in the same row. Once that vaule is found, I need to return it to a cell in Sheet 1.

The data is updated monthly and the number of rows is variable, therefore I cannot use a simple Excel formula based on a specific row. There is also multiple worksheets that I need to reference to return data to Sheet 1, i.e. retrieve values from Sheet 3 and return to Sheet 1, retrieve values from Sheet 4 and return to Sheet 1, etc.

Turn on the macro recorder, then manually complete the steps you describe above. When finished, turn the macro recorder off and examine the generated code. It should be pretty easy to adapt so that you can reuse it as often as you like. — Jon Crowell
– Jon Crowell, Commented Jul 9, 2013 at 17:50

chancea · Accepted Answer · 2013-07-09 18:03:20Z

1

You can use the vlookup formula in sheet1.
Here is an example regarding your example of sheet1 and sheet2 using the text "TOTAL OR":

=VLOOKUP("TOTAL OR",Sheet2!A:B,2,FALSE)

Now in regards to:

The data is updated monthly and the number of rows is variable, therefore I cannot use a simple Excel formula based on a specific row. There is also multiple worksheets that I need to reference to return data to Sheet 1, i.e. retrieve values from Sheet 3 and return to Sheet 1, retrieve values from Sheet 4 and return to Sheet 1, etc.

I do not see this as an issue with VLOOKUP

If I misunderstood you and VLOOKUP cannot work let me know.

answered Jul 9, 2013 at 18:03

chancea

5,9683 gold badges31 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Annemarie Resweber Over a year ago

WOW, awesome!!! I looked online and tried every formula possible using VLOOKUP to no avail!

Collectives™ on Stack Overflow

Excel: search for string in Colum 1 and match to value in column 2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related