CODEWITHSUNDEEP
excelvba
Sona123
Sona123

Reputation: 91

Vlookup and paste the entire row-Applying for entire sheet alternatively

Is it possible to Vlookup a value and copy the entire row that matches

For eg: if I am searching for id=1234 in sheet A

1234 ABC DEF HIJ

The Vlookup should return the entire values.

I am trying to apply this formula in alternate rows of a sheet-The look up value is always in Column A (in the row just above).

Is this possible?

I am a novice in Excel VBA and any help is much appreciated. Thanks

Upvotes: 0

Views: 3934

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

To get the position of an item use the Match Function instead of Vlookup.

position = WorksheetFunction.Match("1234", Worksheets("yoursheet").Columns(1), 0)

Now you can use the position to copy the entire row like

Worksheets("yoursheet").Rows(position).Copy

Upvotes: 0

Rob Anthony
Rob Anthony

Reputation: 1813

You would need a separate VLookup for each piece of data

=Vlookup($A1,Sheet1!$A:$C,Column(Sheet1!B:B),false)

If you use this code in the first cell and copy it across it will give you the correct formula for each piece of data.

I have assumed that the data you are searching for is in cell A1 of the current sheet and that the data you are searching is in Sheet1 between Columns A and C. You will need to modify these values if you are doing something different.

Upvotes: 0

Related Questions