Prev: Why wont my right click functions work?
Next: Text to Columns
From: Joe on 10 Feb 2010 13:34
My spreadsheet is setup as follows:

Column
A B C D
E
County Road Starting Mileage Ending Mileage Road Type
Alan 52 0 5
Highway
Alan 52 5 12
Local Road

When I enter the County, Road, and Mileage point, I want Excel to return to
me the road type. So if I enter Alan-52-3, it will return a value of
"Highway". If I enter Alan-52-7, it would return a value of "Local Road".
How do I do an index/match (or other function) that can search through
columns C & D to determine what mileage point I'm specifically at?
From: Joe on 10 Feb 2010 13:49
The formatting of my original post made it very difficult to follow. Here is
my posting again:

My spreadsheet is setup as follows:

Column A: County
Column B: Road
Column C: Starting Mileage
Column D: Ending Mileage
Column E: Road Type

I want to use an index/match (or other lookup function) to tell me the road
type in column E based on the County - Road - Mileage Point I enter. The
tricky part for me is how to have Excel search through columns C & D to
determine what mileage point I'm at. For example, in one row I have Alan -
52 - 0 - 5 - Highway. In the next row I have Alan - 52 - 5 - 12 - Local
Road. I want Exel to do the following. I enter Alan - 52 - 3 and it returns
the value "Highway". If I enter Alan - 52 - 7, it would return the value of
"Local Road". How do I get Excel to search through columns C & D to
determine the Mileage Point I'm at by comparing the values contained in C & D?


From: Luke M on 10 Feb 2010 13:53
I'll assume that Alan is in cell F2, 52 is in cell F3, and 7 is in cell F4.
Overall "lookup" formula is:
=INDEX(E:E,SUMPRODUCT(--(A2:A100=F2),--(B2:B100=F3),--(C2:C100<=F4),--(D2:D100>=F4),ROW(A2:A100)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Joe" wrote:

> My spreadsheet is setup as follows:
>
> Column
> A B C D
> E
> County Road Starting Mileage Ending Mileage Road Type
> Alan 52 0 5
> Highway
> Alan 52 5 12
> Local Road
>
> When I enter the County, Road, and Mileage point, I want Excel to return to
> me the road type. So if I enter Alan-52-3, it will return a value of
> "Highway". If I enter Alan-52-7, it would return a value of "Local Road".
> How do I do an index/match (or other function) that can search through
> columns C & D to determine what mileage point I'm specifically at?
From: Joe on 11 Feb 2010 08:12
The formula worked great! Now I need to take things one more step. I want
excel to use the following data that was returned to me to perform a new
search:

Inputs:

F4: Road Type (e.g. Local Road, Highway)
F5: Traffic Volume (e.g. 10,000 vehicles / day)
F6: 75% of F5 (e.g. 7,500 vehicles / day)
F7: 125% of F5 (e.g. 12,500 vehicles / day)

Use a formula to search through my table of data and return to me all the
roads that match the criteria in cells F4 and that fall between the range of
values in F6 & F7.

As an example:

F4: Local Road
F6: 7,500 vehicles / day
F7: 12,500 vehicles / day

Outputs from table:
A B C D
Alan | 52 | 7 | 9,300 vehicles / day
Alan | 52 | 23 | 11,000 vehicles / day
Orange | 93 | 4 | 10,000 vehicles / day ...



"Luke M" wrote:

> I'll assume that Alan is in cell F2, 52 is in cell F3, and 7 is in cell F4.
> Overall "lookup" formula is:
> =INDEX(E:E,SUMPRODUCT(--(A2:A100=F2),--(B2:B100=F3),--(C2:C100<=F4),--(D2:D100>=F4),ROW(A2:A100)))
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Joe" wrote:
>
> > My spreadsheet is setup as follows:
> >
> > Column
> > A B C D
> > E
> > County Road Starting Mileage Ending Mileage Road Type
> > Alan 52 0 5
> > Highway
> > Alan 52 5 12
> > Local Road
> >
> > When I enter the County, Road, and Mileage point, I want Excel to return to
> > me the road type. So if I enter Alan-52-3, it will return a value of
> > "Highway". If I enter Alan-52-7, it would return a value of "Local Road".
> > How do I do an index/match (or other function) that can search through
> > columns C & D to determine what mileage point I'm specifically at?
 | 
Pages: 1
Prev: Why wont my right click functions work?
Next: Text to Columns