CODEWITHSUNDEEP
excel
amulbhatia
amulbhatia

Reputation: 33

If value matches in Column then return the row

I have two Excel files A and B each having 4 columns x 5000 rows, which I am using for an audit.

File A has these 5000 rows -

Column A - Account Numbers

Column B - Customer Name

Column C - Limit Sanctioned 

Column D - Outstanding

File B has these rows -

Column A - Account Numbers

Column B - Type of Facility

Column C - Date of Sanction

Column D - Rate of Interest

I want to match the Account Numbers (from File A to File B), If Account Number matches then I should return the full row from File B. Is it possible?

as e.g.

Take Account Number from Column A (File A) and match with the full column A of File B, If Account Number exist. Then return the full Row from that Column of File B.

I am trying with the VLOOKUP ( VALUE, RANGE, INDEX NO.,MATCH)

I took account number as VLOOKUP ('55400055444, A1:A5000,1,FALSE) giving me one cell only. Not the full row, how can I get the full matched row records?

Upvotes: 0

Views: 5346

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

What I would suggest, is have both books open at the same time as it will make your cell addressing a bit easier and less prone to typos.

In column E adjacent in your first row of data, lets assume its row 2, you would use a formula like:

VLOOKUP ($A2, 'filname/sheetname'!$A$2:$D$5000,column(B$1),0)

When you are selecting A2 that is the current workbook you are in. When you select the lookup range, that will be the other workbook. Because you have it open, it will fill in th proper filename format. You can add the $ by pressing F4 right after you select the range, or you can add them in afterwards. Column(B$1) is just a way to count and references the work book where your formula is. once the formula looks right, copy it to the right as far as you need...3 rows. Then select all the rows with the formula and copy down.

When you perform the copy paste operation, cell addresses without the $ will adjust automatically. the cell references with the $ will be locked and wont change.

Upvotes: 1

Related Questions