Is it possible to write a VBA code that searches f

Question

I have two excel wordbooks. One has parameter changes on certain dates and the other has dates on which the juice has been produced. Like this

Spreadsheet 1

date of parameter change        %apples     %oranges
30/09/2014                           55            45
25/09/2014                           50            50
20/09/2014                           45            55

Spreadsheet 2

date of the created juice      %people that liked it    %apples  %oranges
    26/09/2014                     88                                  
    22/09/2014                     91                                  

And I want to copy the % parameters of apples and oranges into the second workbook so that the result looks like

date of the created juice      %people that liked it    %apples  %oranges
    26/09/2014                     88                      50      50                  
    22/09/2014                     91                      45      55

So basically if a juice is made on 26/09 for example then I want to have the parameters that were used for that juice. Obviously the parameters of 30/09 weren't used but the parameters of 25/09 were used because it's the day before the juice has been made.

Is it possible to create something like this with VBA? Could anyone show me how to do the date searching part at least? It would be so much appreciated!

Answer 1

You can use a VLOOKUP formula for what you would like to do rather than using VBA.

The syntax of a VLOOKUP is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

To find an approximate match to the value that you are looking for, you can set the range_lookup parameter to TRUE

Answer 2

If date of parameter change is in Book2 Sheet1 A1 and date of the created juice is in A1 then in C1:

=VLOOKUP($A2,[Book2.xlsx]Sheet1!$A:$C,COLUMN()-1)  

copied across and down may serve provided the date order in Book2 is reversed and both sheets are open at the same time (or the full path would be required).

Answer 3

If you cannot change the sort order of the information in Spreadsheet 1 then a slightly more complex INDEX(MIN(INDEX())) function pair would do.

     

The standard formula in G2 is,

=IFERROR(INDEX(B$2:B$4, MIN(INDEX(ROW($1:$3)+($A$2:$A$4>$E2)*1E+99,,))),"")

This can be copied or filled both right and down. The IFERROR wrapper shows a blank cell when there are no dates that match your criteria (as in 19-Sep-2014 in the image supplied). The sample as produced on a single worksheet for demonstration's sake but the two tables could in in different worksheets or even different workbooks.