Complex VLOOKUP

  • Thread starter Thread starter mluetkem
  • Start date Start date
M

mluetkem

I have a table lookup problem that I can't seem to solve with the basi
VLOOKUP. I have 3 columns of data in the Table_array. They are i
order from l-r, Date, Time, data value. I am trying to populate a
array of Date (column) vs Time (row) for the month of December and al
hours of the day in half hour increments. So the first column of th
array I want the VLOOKUP to fillin would be 01-Dec-03 for the times o
0:00AM, 0:30AM, 1:00AM, 1:30AM, .... 11:30PM. So, where there is n
data in the Table_array for a time for a specific day I would like a
entered, otherwise the corresponding data value in col3 of th
Table_array for that Date & Time combination. I'm basically trying t
take a scattered set of data and popoulating it into a contigious se
of Date & Time data. Any ideas?

Mik
 
Hi mike
can you post some example rows of your source data and some example
rows/columns of your expected result (plain text please, no
attachments).
 
Sure, the source date would look like the following:

Date Time Wait
12/1/2003 5:00 AM 4
12/1/2003 7:00 AM 7
12/1/2003 9:30 AM 2
12/1/2003 11:00 AM 9
12/1/2003 3:00 PM 3
12/1/2003 4:30 PM 1
12/1/2003 7:00 PM 8
12/1/2003 10:00 PM 4
12/2/2003 3:00 AM 3
12/2/2003 5:00 AM 5
12/2/2003 8:30 AM 5
12/2/2003 11:30 AM 7
12/2/2003 3:00 PM 1
12/2/2003 6:30 PM 1
12/2/2003 8:00 PM 4
12/2/2003 10:00 PM 9
12/2/2003 11:30 PM 2
12/3/2003 2:30 AM 1
12/3/2003 3:00 AM 1
12/3/2003 5:30 AM 4
12/3/2003 9:00 AM 9
12/3/2003 1:00 PM 9
12/3/2003 3:30 PM 9
12/3/2003 5:00 PM 5
12/3/2003 9:30 PM 3
12/3/2003 10:00 PM 9

and the reults need to go into a matrix as follows:

Time 12/01/03 12/02/03 12/03/03
12:00 AM
12:30 AM
1:00 AM
1:30 AM
2:00 AM
2:30 AM
3:00 AM
3:30 AM
4:00 AM
 
Hi
try the following formula in your first target cell (assumption: B2)
=INDEX('source_sheet'!$C$1:$C$1000,MATCH(TEXT($A2,"hh:mm")&TEXT(B$1,"MM
DDYYYY"),TEXT('source_sheet'!$B$1:$B$1000,"MMDDYYYY")&TEXT('source_shee
t'!$A$1:$A$1000,"hh:mm"),0))
you have to enter this formula as array formula (CTRL+SHIFT+ENTER)
copy this formula down and to the left
 
Back
Top