Compare Times

[carl]

I have 2 sets of data like so:

Set1

Time Number
121033 5
122345 6
122744 7

Set2

Time Price
121028 1
121533 2
122240 3
122530 4
122645 5

I am trying to build a formula that will be in column 3 of data set1 that
will look at data set2 and pick the price that has time lessthan or equal to
the time on dataset1.

The result would like like so:

set1

Time Number Price
121033 5 1
122345 6 3
122744 7 5

Thank you in advance.

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet2'!$B$1:$B$20,MATCH(MAX(IF('sheet2'!$A$1:$A$20<=A1,'sheet2
'!$A$1:$A$20)),$A$1:$A$20,0))
and copy down

 

[Ron Rosenfeld]

I have 2 sets of data like so:

Set1

Time Number
121033 5
122345 6
122744 7

Set2

Time Price
121028 1
121533 2
122240 3
122530 4
122645 5

I am trying to build a formula that will be in column 3 of data set1 that
will look at data set2 and pick the price that has time lessthan or equal to
the time on dataset1.

The result would like like so:

set1

Time Number Price
121033 5 1
122345 6 3
122744 7 5

Thank you in advance.

If the times in Set 2 are sorted in ascending order, as in your example, then:

=VLOOKUP(A2,Set2,2)

Where A2 contains a Time from Set1; Set2 is a defined name encompassing that
table, (and 2 is the second column in Data Set2 which is where the Price is
stored).


--ron