LinkBack URL
About LinkBacks
Hi team,
I have two worksheets: a reference sheet and an incomplete sheet
Each value in column B of the incomplete sheet lies between the values from one row in columns B and C of the reference sheet. I want to return the values in columns D, E and F from that row of the reference sheet into the incomplete sheet.
To write it all in logical argument, for row 1 of 'Incomplete':
If A1 ('Reference') = 1 (i.e. the value in A1 ('Incomplete')), does B1 ('Incomplete') lie between B1 ('Reference') and C1 ('Reference')
If not, does A2 ('Reference') = 1; if yes, does B1 ('Incomplete') lie between B2 ('Reference') and C2 ('Reference')
and so on, until it finds a match.
Then, copy values from Dx, Ex and Fx of 'Reference' into D1, E1, and F1 of 'Incomplete'.
There should only be one match for each line, so once a match is found, the process can stop and move on to the next row of 'Incomplete' until all rows are matched.
Thus far:
I have tried using VLOOKUP, but the numbers often are not close enough to be a 'near match'.
I have also tried IF (to make sure that only those rows with a value in column A ('Reference') that match the value in column A ('Incomplete') are searched) and INDEX/MATCH . Two problems:
1. I can't seem to get it to work
2. This workbook is a sample only. I have >400 spreadsheets to do this on (each with 10-40 rows). I won't actually know which rows to include in the INDEX/MATCH argument to ensure the value in A (incomplete) matches the value in A (reference)
Would appreciate any help
Kind regards
Adam
Register To Reply
Bookmarks