What Function to use?

[Peter Barrett]

Apologies - I think my oil filter is blocked and my brain is lacking all
lubrication!

I cannot owrk out what function to use to select values from a table - can
anyone please advise?

In simple terms, I am trying to manipultae data as shown below:

Input Output
TABLE OF DATA

Date Airport Sunrise FRA
MAN
1 August 2005 MAN 04:25 03:54 04:25
2 August 2005 FRA 03:56 03:56 04:26

For each row, I want to input an airport (e.g. MAN, FRA) and obtain the
correct sunrise time for that airport. There is a table of daily sunrise
times for the airports - which is from where I want to be able to extract the
data.

Can anyone tell me how to do this - I have tried LOOKUP without any success.

Thanks in advance

Peter

 

[Bernie Deitrick]

Peter,

=VLOOKUP(Cell with code,Table with codes and sunrises,2,False)

where Table with codes and sunrises has the airport as the first column, with sunrise as the second
column.

HTH,
Bernie
MS Excel MVP

 

[Peter Barrett]

Bernie

Thanks for the advice.

I have tried to experiment with your suggestion and am unable to achieve the
result that I want - as yet!

If i re-arrange the data so that the data table hos just two columns
[Airport Code, Sunrise Time] (as I think your formula requires - let me know
if I have misinterpretted this), how do I code the vlookup formula so that it
picks up the sunirise time for the required airport for the required date.
The table will contain many many sunrises for a particular airport - one for
each day of the year.

Thanks in advance for any further light that you can shed on this.

Peter

 

[Bernie Deitrick]

Peter,

I now understand. You can use VLOOKUP, but you would need to create a column of unique codes on
which to base the lookup, but there are better ways.

So, let's say that your dates are in column A, and your airport codes are in column B, and your
sunrise times are in column C, and you have 1000 rows of data. In D1, enter the date of interest,
and in E1, enter the airport code of interest. Assuming you have only one entry per date per
airport, then you could use this formula

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000))

If you have more than 1000 rows, increase the 1000 in each of the three places.

One complication may be how your dates are entered: make sure that the formula

=D1=A???

where A??? is a cell that you THINK matches the date in D1, actually returns TRUE.

HTH,
Bernie
MS Excel MVP

Bernie

Thanks for the advice.

I have tried to experiment with your suggestion and am unable to achieve the
result that I want - as yet!

If i re-arrange the data so that the data table hos just two columns
[Airport Code, Sunrise Time] (as I think your formula requires - let me know
if I have misinterpretted this), how do I code the vlookup formula so that it
picks up the sunirise time for the required airport for the required date.
The table will contain many many sunrises for a particular airport - one for
each day of the year.

Thanks in advance for any further light that you can shed on this.

Peter

Peter,

=VLOOKUP(Cell with code,Table with codes and sunrises,2,False)

where Table with codes and sunrises has the airport as the first column, with sunrise as the
second
column.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Peter,

I should have added that the cell with the formula should be formatted as time, and will only work
if the times are true times and not strings. If the times are strings, you can change the formula to
account for that as well.

HTH,
Bernie
MS Excel MVP

Peter,

I now understand. You can use VLOOKUP, but you would need to create a column of unique codes on
which to base the lookup, but there are better ways.

So, let's say that your dates are in column A, and your airport codes are in column B, and your
sunrise times are in column C, and you have 1000 rows of data. In D1, enter the date of interest,
and in E1, enter the airport code of interest. Assuming you have only one entry per date per
airport, then you could use this formula

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000))

If you have more than 1000 rows, increase the 1000 in each of the three places.

One complication may be how your dates are entered: make sure that the formula

=D1=A???

where A??? is a cell that you THINK matches the date in D1, actually returns TRUE.

HTH,
Bernie
MS Excel MVP

Bernie

Thanks for the advice.

I have tried to experiment with your suggestion and am unable to achieve the
result that I want - as yet!

If i re-arrange the data so that the data table hos just two columns
[Airport Code, Sunrise Time] (as I think your formula requires - let me know
if I have misinterpretted this), how do I code the vlookup formula so that it
picks up the sunirise time for the required airport for the required date.
The table will contain many many sunrises for a particular airport - one for
each day of the year.

Thanks in advance for any further light that you can shed on this.

Peter

Peter,

=VLOOKUP(Cell with code,Table with codes and sunrises,2,False)

where Table with codes and sunrises has the airport as the first column, with sunrise as the
second
column.

HTH,
Bernie
MS Excel MVP


Apologies - I think my oil filter is blocked and my brain is lacking all
lubrication!

I cannot owrk out what function to use to select values from a table - can
anyone please advise?

In simple terms, I am trying to manipultae data as shown below:

Input Output
TABLE OF DATA

Date Airport Sunrise FRA
MAN
1 August 2005 MAN 04:25 03:54 04:25
2 August 2005 FRA 03:56 03:56 04:26

For each row, I want to input an airport (e.g. MAN, FRA) and obtain the
correct sunrise time for that airport. There is a table of daily sunrise
times for the airports - which is from where I want to be able to extract the
data.

Can anyone tell me how to do this - I have tried LOOKUP without any success.

Thanks in advance

Peter

 

[Peter Barrett]

Bernie

Thanks for this - I have got the spreadsheet working now.

All the best.

Peter

Peter,

I should have added that the cell with the formula should be formatted as time, and will only work
if the times are true times and not strings. If the times are strings, you can change the formula to
account for that as well.

HTH,
Bernie
MS Excel MVP

Peter,

I now understand. You can use VLOOKUP, but you would need to create a column of unique codes on
which to base the lookup, but there are better ways.

So, let's say that your dates are in column A, and your airport codes are in column B, and your
sunrise times are in column C, and you have 1000 rows of data. In D1, enter the date of interest,
and in E1, enter the airport code of interest. Assuming you have only one entry per date per
airport, then you could use this formula

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000))

If you have more than 1000 rows, increase the 1000 in each of the three places.

One complication may be how your dates are entered: make sure that the formula

=D1=A???

where A??? is a cell that you THINK matches the date in D1, actually returns TRUE.

HTH,
Bernie
MS Excel MVP

Bernie

Thanks for the advice.

I have tried to experiment with your suggestion and am unable to achieve the
result that I want - as yet!

If i re-arrange the data so that the data table hos just two columns
[Airport Code, Sunrise Time] (as I think your formula requires - let me know
if I have misinterpretted this), how do I code the vlookup formula so that it
picks up the sunirise time for the required airport for the required date.
The table will contain many many sunrises for a particular airport - one for
each day of the year.

Thanks in advance for any further light that you can shed on this.

Peter

:

Peter,

=VLOOKUP(Cell with code,Table with codes and sunrises,2,False)

where Table with codes and sunrises has the airport as the first column, with sunrise as the
second
column.

HTH,
Bernie
MS Excel MVP


Apologies - I think my oil filter is blocked and my brain is lacking all
lubrication!

I cannot owrk out what function to use to select values from a table - can
anyone please advise?

In simple terms, I am trying to manipultae data as shown below:

Input Output
TABLE OF DATA

Date Airport Sunrise FRA
MAN
1 August 2005 MAN 04:25 03:54 04:25
2 August 2005 FRA 03:56 03:56 04:26

For each row, I want to input an airport (e.g. MAN, FRA) and obtain the
correct sunrise time for that airport. There is a table of daily sunrise
times for the airports - which is from where I want to be able to extract the
data.

Can anyone tell me how to do this - I have tried LOOKUP without any success.

Thanks in advance

Peter