VLookup or Index-Match??

G

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
748
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi,

In the following table I would like to pick the data from Col E and Col H whose Day and HOUR is matching with Day and HOUR of Col A and put in Col B and Col C respectively.

For Example in the first row of Col B and Col C is BLANK & 10.1 What is the formula I should use in Column B and Column C?
Col ACol BCol CCol DCol ECol FCol GCol H
7/10/20 8:25 AM​
7/5/20 2:00 AM​
47.66​
7/5/20 8:30 AM​
10​
7/10/20 2:30 AM​
7/5/20 2:00 PM​
40.22​
7/5/20 8:15 PM​
9.9​
7/9/20 8:45 PM​
7/6/20 2:00 AM​
35.16​
7/6/20 8:20 AM​
9.9​
7/9/20 2:30 PM​
7/6/20 2:10 PM​
33.51​
7/6/20 8:15 PM​
9.8​
7/9/20 8:30 AM​
7/7/20 2:30 AM​
33.62​
7/7/20 8:00 AM​
9.9​
7/9/20 2:30 AM​
7/7/20 1:00 PM​
34.01​
7/7/20 8:30 PM​
10.1​
7/8/20 8:50 PM​
7/8/20 2:00 AM​
35.3​
7/8/20 8:25 AM​
10.1​
7/8/20 2:25 PM​
7/8/20 2:25 PM​
40.72​
7/8/20 8:50 PM​
10.1​
7/8/20 8:40 AM​
7/9/20 2:30 AM​
36.59​
7/9/20 8:09 AM​
10.1​
7/8/20 2:00 AM​
7/9/20 2:30 PM​
30.99​
7/9/20 8:30 PM​
10.2​
7/7/20 8:30 PM​
7/10/20 2:00 AM​
36.95​
7/10/20 8:25 AM​
10.1​
7/7/20 1:00 PM​
7/10/20 2:15 PM​
38.05​
7/10/20 8:20 PM​
10.2​
7/7/20 8:45 AM​
7/11/20 12:15 AM​
10.2​
7/7/20 2:30 AM​
7/6/20 8:15 PM​
7/6/20 2:10 PM​
7/6/20 8:40 AM​
7/6/20 2:00 AM​
7/5/20 8:15 PM​
7/5/20 2:00 PM​
7/5/20 8:30 AM​
7/5/20 2:00 AM​
7/4/20 8:20 PM​
7/4/20 3:35 PM​
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Book2
ABCDEFGH
17/10/2020 8:25 107/5/2020 2:0047.77/5/2020 8:3010
27/10/2020 2:30  7/5/2020 14:0040.27/5/2020 20:159.9
37/9/2020 20:45  7/6/2020 2:0035.27/6/2020 8:209.9
47/9/2020 14:3031 7/6/2020 14:1033.57/6/2020 20:159.8
57/9/2020 8:30  7/7/2020 2:3033.67/7/2020 8:009.9
67/9/2020 2:3036.6 7/7/2020 13:00347/7/2020 20:3010
77/8/2020 20:50 107/8/2020 2:0035.37/8/2020 8:2510
87/8/2020 14:2540.7 7/8/2020 14:2540.77/8/2020 20:5010
97/8/2020 8:40  7/9/2020 2:3036.67/9/2020 8:0910
107/8/2020 2:0035.3 7/9/2020 14:30317/9/2020 20:3010
117/7/2020 20:30 107/10/2020 2:00377/10/2020 8:2510
127/7/2020 13:0034 7/10/2020 14:1538.17/10/2020 20:2010
137/7/2020 8:45  7/11/2020 0:1510
147/7/2020 2:3033.6 
157/6/2020 20:15 9.8
167/6/2020 14:1033.5 
177/6/2020 8:40  
187/6/2020 2:0035.2 
197/5/2020 20:15 9.9
207/5/2020 14:0040.2 
217/5/2020 8:30 10
227/5/2020 2:0047.7 
237/4/2020 20:20  
247/4/2020 15:35  
Sheet5
Cell Formulas
RangeFormula
B1:B24B1=IFERROR(INDEX($D$1:$H$13,MATCH($A1,$D$1:$D$13,0),2),"")
C1:C24C1=IFERROR(INDEX($D$1:$H$13,MATCH($A1,$G$1:$G$13,0),5),"")
 
Upvote 0
Thanks JoeMo,

The formula giving the result if the date and time is exactly matching. Please find below the new range of data. For simplicity I rounded the Col A date to nearest hour. If any of the date matching within this day and hour of Col A with Col C then Col B shall pick the value from Col D. Hope I made point clear. Sample result table also I posted here.
Sample Data
Col ACol BCol CCol D
11-Jul-20 08:0005-Jul-20 02:00
47.65999985​
11-Jul-20 02:0005-Jul-20 14:00
40.22000122​
11-Jul-20 00:0006-Jul-20 02:00
35.15999985​
10-Jul-20 20:0006-Jul-20 14:10
33.50999832​
10-Jul-20 14:0007-Jul-20 02:30
33.61999893​
10-Jul-20 08:0007-Jul-20 13:00
34.00999832​
10-Jul-20 02:0008-Jul-20 02:00
35.29999924​
09-Jul-20 20:0008-Jul-20 14:25
40.72000122​
09-Jul-20 14:0009-Jul-20 02:30
36.59000015​
09-Jul-20 08:0009-Jul-20 14:30
30.98999977​
09-Jul-20 02:0010-Jul-20 02:00
36.95000076​
08-Jul-20 20:0010-Jul-20 14:15
38.04999924​
08-Jul-20 14:0011-Jul-20 02:10
39.06000137​
08-Jul-20 08:00
08-Jul-20 02:00
07-Jul-20 20:00
07-Jul-20 13:00
07-Jul-20 08:00
07-Jul-20 02:00
06-Jul-20 20:00
06-Jul-20 14:00
06-Jul-20 08:00
06-Jul-20 02:00
05-Jul-20 20:00
05-Jul-20 14:00
05-Jul-20 08:00
05-Jul-20 02:00


Result Data
Col ACol BCol CCol D
11-Jul-20 08:0005-Jul-20 02:00
47.66​
11-Jul-20 02:0039.0605-Jul-20 14:00
40.22​
11-Jul-20 00:0006-Jul-20 02:00
35.16​
10-Jul-20 20:0006-Jul-20 14:10
33.51​
10-Jul-20 14:0038.0507-Jul-20 02:30
33.62​
10-Jul-20 08:0007-Jul-20 13:00
34.01​
10-Jul-20 02:0036.9508-Jul-20 02:00
35.3​
09-Jul-20 20:0008-Jul-20 14:25
40.72​
09-Jul-20 14:0030.9909-Jul-20 02:30
36.59​
09-Jul-20 08:0009-Jul-20 14:30
30.99​
09-Jul-20 02:0036.5910-Jul-20 02:00
36.95​
08-Jul-20 20:0010-Jul-20 14:15
38.05​
08-Jul-20 14:0040.7211-Jul-20 02:10
39.06​
08-Jul-20 08:00
08-Jul-20 02:0035.3
07-Jul-20 20:00
07-Jul-20 13:0034.01
07-Jul-20 08:00
07-Jul-20 02:0033.62
06-Jul-20 20:00
06-Jul-20 14:0033.51
06-Jul-20 08:00
06-Jul-20 02:0035.16
05-Jul-20 20:00
05-Jul-20 14:0040.22
05-Jul-20 08:00
05-Jul-20 02:0047.66
 
Upvote 0
What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.
 
Upvote 0
The formulas I posted in reply to your OP are looking for an exact match for a col A value in col C. For your new layout in post #3, rounding col A "for simplicity", if it removes exact matches to col C data, will not work. If col C data are arranged in ascending order, the formulas can be changed to find the largest value in col C that is less than or equal to the col A value and then return the col D companion value, but the results will not be the values you posted in your desired "Result Data" col B.
 
Upvote 0
Fluff said:
What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.
Click to expand...
Thanks I updated my account details..
 
Upvote 0
JoeMo said:
The formulas I posted in reply to your OP are looking for an exact match for a col A value in col C. For your new layout in post #3, rounding col A "for simplicity", if it removes exact matches to col C data, will not work. If col C data are arranged in ascending order, the formulas can be changed to find the largest value in col C that is less than or equal to the col A value and then return the col D companion value, but the results will not be the values you posted in your desired "Result Data" col B.
Click to expand...

Col C will always be in ascending order. Let me try with your suggestion and come back.
 
Upvote 0
gnaga said:
Thanks I updated my account details..
Click to expand...
Thanks.
How about
+Fluff New.xlsm
ABCD
1Col ACol BCol CCol D
211/07/2020 08:00 05/07/2020 02:0047.66
311/07/2020 02:0039.0605/07/2020 14:0040.22
411/07/2020 00:00 06/07/2020 02:0035.16
510/07/2020 20:00 06/07/2020 14:1033.51
610/07/2020 14:0038.0507/07/2020 02:3033.62
710/07/2020 08:00 07/07/2020 13:0034.01
810/07/2020 02:0036.9508/07/2020 02:0035.30
909/07/2020 20:00 08/07/2020 14:2540.72
1009/07/2020 14:0030.9909/07/2020 02:3036.59
1109/07/2020 08:00 09/07/2020 14:3030.99
1209/07/2020 02:0036.5910/07/2020 02:0036.95
1308/07/2020 20:00 10/07/2020 14:1538.05
1408/07/2020 14:0040.7211/07/2020 02:1039.06
1508/07/2020 08:00 
1608/07/2020 02:0035.30
1707/07/2020 20:00 
1807/07/2020 13:0034.01
1907/07/2020 08:00 
2007/07/2020 02:0033.62
2106/07/2020 20:00 
2206/07/2020 14:0033.51
2306/07/2020 08:00 
2406/07/2020 02:0035.16
2505/07/2020 20:00 
2605/07/2020 14:0040.22
2705/07/2020 08:00 
2805/07/2020 02:0047.66
Main
Cell Formulas
RangeFormula
B2:B28B2=IFERROR(INDEX($D$2:$D$14,MATCH(TEXT(A2,"dd/mm/yy hh"),TEXT($C$2:$C$14,"dd/mm/yy hh"),0)),"")


May need to be confirmed with Ctrl Shift Enter, if you don't have dynamic arrays.
 
Upvote 0
Fluff said:
Thanks.
How about
+Fluff New.xlsm
ABCD
1Col ACol BCol CCol D
211/07/2020 08:00 05/07/2020 02:0047.66
311/07/2020 02:0039.0605/07/2020 14:0040.22
411/07/2020 00:00 06/07/2020 02:0035.16
510/07/2020 20:00 06/07/2020 14:1033.51
610/07/2020 14:0038.0507/07/2020 02:3033.62
710/07/2020 08:00 07/07/2020 13:0034.01
810/07/2020 02:0036.9508/07/2020 02:0035.30
909/07/2020 20:00 08/07/2020 14:2540.72
1009/07/2020 14:0030.9909/07/2020 02:3036.59
1109/07/2020 08:00 09/07/2020 14:3030.99
1209/07/2020 02:0036.5910/07/2020 02:0036.95
1308/07/2020 20:00 10/07/2020 14:1538.05
1408/07/2020 14:0040.7211/07/2020 02:1039.06
1508/07/2020 08:00 
1608/07/2020 02:0035.30
1707/07/2020 20:00 
1807/07/2020 13:0034.01
1907/07/2020 08:00 
2007/07/2020 02:0033.62
2106/07/2020 20:00 
2206/07/2020 14:0033.51
2306/07/2020 08:00 
2406/07/2020 02:0035.16
2505/07/2020 20:00 
2605/07/2020 14:0040.22
2705/07/2020 08:00 
2805/07/2020 02:0047.66
Main
Cell Formulas
RangeFormula
B2:B28B2=IFERROR(INDEX($D$2:$D$14,MATCH(TEXT(A2,"dd/mm/yy hh"),TEXT($C$2:$C$14,"dd/mm/yy hh"),0)),"")


May need to be confirmed with Ctrl Shift Enter, if you don't have dynamic arrays.
Click to expand...

Thanks Fluff.. This is what exactly I want.. Thank you very much your time and effort..
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Hi everyone. Help wit Excel!
Replies
2
Views
114
StephenCrump
S
C
  • Solved
Calculate difference b/w numbers
Replies
3
Views
139
Peter_SSs
Peter_SSs
A
  • Question
Excel Formula.
Replies
1
Views
188
StephenCrump
S
S
  • Solved
Average in excel
Replies
3
Views
65
etaf
etaf
R
  • Question
Unified DDMMYY with variable data
2
Replies
11
Views
280
Alex Blakenburg
A

Forum statistics

Threads
1,214,987
Messages
6,122,618
Members
449,092
Latest member
amyap

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top