Vb macro

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:00 PM GMT

Question

I need to create a function to return a value from one worksheet back to another. Using a field on WS1, search WS2 and return back to WS1 the value from a corresponding column in WS2.

For every cell in WS1 columnA, find corresponding value in WS2 Column B and capture rownumber. Then return to WS1 columnG for that rownumber.

WS1 contains 200 entries. WS2 contains 10000 entries.

Answer

The following function will do what you wanted: For cell A1 in WS1 columnA, lookup the corresponding value in WS2 Column B and capture the row number. Using the captured row number, return to WS1 column G for that row number. The function can be placed in cell B1 and copied down.

=INDIRECT("G"&MATCH(A1,Sheet2!B:B,0))

Hope this answers your question.

Advertisement

©2017 eLuminary LLC. All rights reserved.