Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Compare Times
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. |
#2
|
|||
|
|||
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 -- Regards Frank Kabel Frankfurt, Germany "carl" schrieb im Newsbeitrag ... 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. |
#3
|
|||
|
|||
On Fri, 3 Dec 2004 07:33:02 -0800, "carl"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to compare 2 spreadsheets with Excel? | Excel Discussion (Misc queries) | |||
Adding/Averaging Times in Excel | Excel Discussion (Misc queries) | |||
Whats the function to count the total times a word is displayed | Excel Discussion (Misc queries) | |||
Compare Columns | Excel Discussion (Misc queries) | |||
How can use IF function more than 7 times in a cell | Excel Worksheet Functions |