help in using vlookup for finding a record in another workbook

[civram1982]

hi everyone just want to ask about vlookup im having a problem. please check my attached excel file for more info.

thank you in advance!

[Pete_UK]

You need to give each record its own unique reference. You can do this by putting this formula in A7 of the dwg_list file:

=IF(B7="","",B7&"_"&COUNTIF(B$7:B7,B7))

Then copy this down - do you see that it has added a unique sequential number to the end of the subref?

Then your VLOOKUP in the other file can start of with:

=VLOOKUP($B$2&"_"&ROWS($1:1), ...

and when this is copied down the ROWS($1:1) term will change on successive rows to return 1, 2, 3, 4 etc., enabling you to extract each multiple row in turn.

Hope this helps.

Pete

[civram1982]

Then your VLOOKUP in the other file can start of with:

=VLOOKUP($B$2&"_"&ROWS($1:1), ...

and when this is copied down the ROWS($1:1) term will change on successive rows to return 1, 2, 3, 4 etc., enabling you to extract each multiple row in turn.

Hope this helps.

Pete

hi Pete_UK

thank you for your reply Pete_UK. sorry for the late reply i try the formula but still the same on my sample.

here what i do based on your formula.
=VLOOKUP($B$2&"_"&ROWS(1:1),'[dwg list.xlsx]dwglist'!$A$7:$E$276,2,0)

and also i notice that if i want to make another reference it will replace the data on the log its not adding a new data.

thank you in advance

[benishiryo]

hi civram. you missed out a crucial dollar sign from Pete's suggestion:
=VLOOKUP($B$2&"_"&ROWS($1:1),'[dwg list.xlsx]dwglist'!$A$7:$E$276,2,0)

you also need to make the col_index_num increase. so...
=VLOOKUP($B$2&"_"&ROWS($1:1),'[dwg list.xlsx]dwglist'!$A$7:$E$276,COLUMNS($A8:A8)+1,0)

and if possible, can they both in in 1 workbook instead of 2? excel works harder when they are in 2 different workbooks.

[civram1982]

hi benishiryo

thank you for your reply and sorry for the late response i try the formula its working but the problem is when i add a new sub ref. all the details in log will change. what im trying to do is for example i enter sub ref 001 all the details will show in the log and i add another sub ref 002 so on.. this log will copy the details in the list and store it.

thank you in advanced!