I have the sheet 1 that contains a dataset from columns A to Z enter image description here

And a second sheet sheet 2 that has a dataset from column A to B only enter image description here

The above sheets have their first column a time value assignment.

How can I create a sheet 3 where the column B in sheet 2 will be assigned to all the columns in sheet 1 where if and only if it meets this criteria: The search should start in sheet 2 row by row and at each iteration, the value of column A in sheet 2 or the time and then comparing it with the column A in sheet 1 where if the time value in sheet 1 is 30 minutes earlier than the one picked in sheet 2 then here the condition is true and hence the compliant row in sheet 1 will be merged with its corresponding value in column B in sheet 2.

Should the condition is not true, then the iteration described above in sheet should jump to next row for further comparisons.

  • sounds like a vlookup, but it's not very clear – Raystafarian Mar 16 '16 at 14:10

I believe that your best bet is to format the data in the sheets as a Table (so you can use column names in your formulas for clarity's sake) then use INDEX / MATCH method of lookups.

If I understand your question correctly, I would recommend creating one column that would return a 1 or 0 to determine if your desired match/condition existed for including the row data, then the remaining columns in Sheet 3 could use the INDEX / MATCH to pull the data from Sheet 1.

See here for a good primer on INDEX / MATCH: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.