Looking up values in multiple rows

  • Thread starter Thread starter MeMe
  • Start date Start date
M

MeMe

Hi there,

I need to have a vlookup or something that works across rows.

For example, I have column A representing time, with A1 through to Axxxx
with each cell showing a time for a specific date. So for June 1 we would see
the times 08:00, 08:30, 09:00, etc. Then for June 2 we would see the times
08:00, 08:30, 09:00, etc.. For the entire month these same times are noted.
Column C is the column that has the data that I want, but I only want a
specific time.

How do I set this up so that my formula gives me the column C data for 12:30
for every day in June.

Thanks,
Marie
 
One way...

Assuming your dates/times are true Excel dates/times...

Enter the first day of the month for the date/time you want to lookup in a
cell:

E1 = 6/1/2009 12:30 PM

Enter this formula in F1 and copy down as needed:

=INDEX(C:C,MATCH(E$1+ROWS(F$1:F1)-1,A:A,0))
 
Can I send it to you?

T. Valko said:
One way...

Assuming your dates/times are true Excel dates/times...

Enter the first day of the month for the date/time you want to lookup in a
cell:

E1 = 6/1/2009 12:30 PM

Enter this formula in F1 and copy down as needed:

=INDEX(C:C,MATCH(E$1+ROWS(F$1:F1)-1,A:A,0))
Click to expand...
 
You can upload your file (with size restrictions) or a smaller sample file
to a free file host then post a link to the file. Then anyone that's
interested can look at the file.

I use this free file host often:

http://tinyurl.com/24xfnt

It's a French site that's been translated to English.
 
I posted it, but have never used the site.

Am welcoming ideas. I guess I just wait and see.

Thanks,

Marie
 
Hello Don-

I seem to have a similar problem. I am having problems with MATCH setup. I
have a file with 5 fields in which data in each field is different if
concatenated together which will produce a unique identifier. However, I am
not interested in combining them together, but in using the MATCH function 5
times to obtain the successfully identify these unique fields and the
quantity value will be assigned or imported onto another spreadsheet in the
corresponding field. I understand from reading a previous blog that MATCH
function can be used up to 7 times. I need someone to give me an example.
Here is what I have for my example so far:
=INDEX($A$43:$K$53,MATCH(P47,$F$43:$F$53,0),7).
This works ok when conducting only 1 match, however, I need 4 more. Please
let me know how to include additional MATCH functions in this script. Also,
this script is testing on the same worksheet. Please let me know the script
to include to reference another workbook and worksheet.

Thanks-
Neecy
 
Back
Top