Vlookup Multiple Returns_Answers

I am using excel as a scheduling software for construction. I have certain
phases that may take 1 or more days. I am trying to type in the job number
and have it return all the days each phase is scheduled on.

I currently use the following Vlookup to find the first day:
=VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
e5=Job Number
b11=Phase Type
Data!$E$1:$K$16200= the table_array I am pulling from.
4=the co-_index_num

How can I have it return every match for this job number and phase type,
with each match in a seperate row under each other?
--
Thanks,

Ben

Hi!

Here's one way.

Column 4 in your table array is column H.

Entered with the key combo of CTRL,SHIFT,ENTER:

=INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1)))

Copy down until you get #NUM! errors meaning all matches have been found.

If you don't want to see the #NUM! errors you could suppress their display
by including an error trap in the formula. This makes the formula twice as
long and since you're looking through a large range, may "slow" things down
a bit. An alternative is to use conditional formatiing to "hide" the errors.

Post back if you need further assistance in dealing with the #NUM! errors.

Biff

"Ben" <Ben@discussions.microsoft.com> wrote in message
news:31C5603C-AF2D-4C11-A79E-FC2EFA299165@microsoft.com...
>I am using excel as a scheduling software for construction. I have certain
> phases that may take 1 or more days. I am trying to type in the job
> number
> and have it return all the days each phase is scheduled on.
>
> I currently use the following Vlookup to find the first day:
> =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
> e5=Job Number
> b11=Phase Type
> Data!$E$1:$K$16200= the table_array I am pulling from.
> 4=the co-_index_num
>
> How can I have it return every match for this job number and phase type,
> with each match in a seperate row under each other?
> --
> Thanks,
>
> Ben

Biff that works Great! Thanks!

I tried putting isnumber around the formula to get rid of the #num but that
didn't work any ideas would be greatly appreciated!
--
Thanks,

Ben


"Biff" wrote:

> Hi!
>
> Here's one way.
>
> Column 4 in your table array is column H.
>
> Entered with the key combo of CTRL,SHIFT,ENTER:
>
> =INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1)))
>
> Copy down until you get #NUM! errors meaning all matches have been found.
>
> If you don't want to see the #NUM! errors you could suppress their display
> by including an error trap in the formula. This makes the formula twice as
> long and since you're looking through a large range, may "slow" things down
> a bit. An alternative is to use conditional formatiing to "hide" the errors.
>
> Post back if you need further assistance in dealing with the #NUM! errors.
>
> Biff
>
> "Ben" <Ben@discussions.microsoft.com> wrote in message
> news:31C5603C-AF2D-4C11-A79E-FC2EFA299165@microsoft.com...
> >I am using excel as a scheduling software for construction. I have certain
> > phases that may take 1 or more days. I am trying to type in the job
> > number
> > and have it return all the days each phase is scheduled on.
> >
> > I currently use the following Vlookup to find the first day:
> > =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
> > e5=Job Number
> > b11=Phase Type
> > Data!$E$1:$K$16200= the table_array I am pulling from.
> > 4=the co-_index_num
> >
> > How can I have it return every match for this job number and phase type,
> > with each match in a seperate row under each other?
> > --
> > Thanks,
> >
> > Ben
>
>
>

Biff this worked great until I inserted more rows since I hadn't recieved a
#NUM yet. Now all the cells come up with #N/A.

How did I mess that up?

--
Thanks,

Ben


"Biff" wrote:

> Hi!
>
> Here's one way.
>
> Column 4 in your table array is column H.
>
> Entered with the key combo of CTRL,SHIFT,ENTER:
>
> =INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1)))
>
> Copy down until you get #NUM! errors meaning all matches have been found.
>
> If you don't want to see the #NUM! errors you could suppress their display
> by including an error trap in the formula. This makes the formula twice as
> long and since you're looking through a large range, may "slow" things down
> a bit. An alternative is to use conditional formatiing to "hide" the errors.
>
> Post back if you need further assistance in dealing with the #NUM! errors.
>
> Biff
>
> "Ben" <Ben@discussions.microsoft.com> wrote in message
> news:31C5603C-AF2D-4C11-A79E-FC2EFA299165@microsoft.com...
> >I am using excel as a scheduling software for construction. I have certain
> > phases that may take 1 or more days. I am trying to type in the job
> > number
> > and have it return all the days each phase is scheduled on.
> >
> > I currently use the following Vlookup to find the first day:
> > =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
> > e5=Job Number
> > b11=Phase Type
> > Data!$E$1:$K$16200= the table_array I am pulling from.
> > 4=the co-_index_num
> >
> > How can I have it return every match for this job number and phase type,
> > with each match in a seperate row under each other?
> > --
> > Thanks,
> >
> > Ben
>
>
>

Here's the error trap in the formula, entered with CSE:

=IF(ISERROR(SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1))),"",INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1))))

You have to copy the formula to enough cells that will hold all the possible
returns. How many will that be????

It's more efficient to use conditional formatting and use the shorter
formula:

Select the range of cells where the formula will be entered. I'll use A1:A50
as an example:

Select the range A1:A50
Goto Format>Conditional Formatting
Formula is: =ISERROR(A1)
Click the Format button
Set the font color to be the same as the fill color
OK out

Biff

"Ben" <Ben@discussions.microsoft.com> wrote in message
news:9AE887EF-11D7-416A-A33E-666C85C5F5FC@microsoft.com...
> Biff that works Great! Thanks!
>
> I tried putting isnumber around the formula to get rid of the #num but
> that
> didn't work any ideas would be greatly appreciated!
> --
> Thanks,
>
> Ben
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Here's one way.
>>
>> Column 4 in your table array is column H.
>>
>> Entered with the key combo of CTRL,SHIFT,ENTER:
>>
>> =INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1)))
>>
>> Copy down until you get #NUM! errors meaning all matches have been found.
>>
>> If you don't want to see the #NUM! errors you could suppress their
>> display
>> by including an error trap in the formula. This makes the formula twice
>> as
>> long and since you're looking through a large range, may "slow" things
>> down
>> a bit. An alternative is to use conditional formatiing to "hide" the
>> errors.
>>
>> Post back if you need further assistance in dealing with the #NUM!
>> errors.
>>
>> Biff
>>
>> "Ben" <Ben@discussions.microsoft.com> wrote in message
>> news:31C5603C-AF2D-4C11-A79E-FC2EFA299165@microsoft.com...
>> >I am using excel as a scheduling software for construction. I have
>> >certain
>> > phases that may take 1 or more days. I am trying to type in the job
>> > number
>> > and have it return all the days each phase is scheduled on.
>> >
>> > I currently use the following Vlookup to find the first day:
>> > =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
>> > e5=Job Number
>> > b11=Phase Type
>> > Data!$E$1:$K$16200= the table_array I am pulling from.
>> > 4=the co-_index_num
>> >
>> > How can I have it return every match for this job number and phase
>> > type,
>> > with each match in a seperate row under each other?
>> > --
>> > Thanks,
>> >
>> > Ben
>>
>>
>>

> How did I mess that up?

By inserting new rows! <g>

Make sure the range: Data!E$1:E$16200 and ROW($1:$16200) are the EXACT same
size.

Biff

"Ben" <Ben@discussions.microsoft.com> wrote in message
news:0CA65434-54F3-4FE0-B6EC-880288F9BB07@microsoft.com...
> Biff this worked great until I inserted more rows since I hadn't recieved
> a
> #NUM yet. Now all the cells come up with #N/A.
>
> How did I mess that up?
>
> --
> Thanks,
>
> Ben
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Here's one way.
>>
>> Column 4 in your table array is column H.
>>
>> Entered with the key combo of CTRL,SHIFT,ENTER:
>>
>> =INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1)))
>>
>> Copy down until you get #NUM! errors meaning all matches have been found.
>>
>> If you don't want to see the #NUM! errors you could suppress their
>> display
>> by including an error trap in the formula. This makes the formula twice
>> as
>> long and since you're looking through a large range, may "slow" things
>> down
>> a bit. An alternative is to use conditional formatiing to "hide" the
>> errors.
>>
>> Post back if you need further assistance in dealing with the #NUM!
>> errors.
>>
>> Biff
>>
>> "Ben" <Ben@discussions.microsoft.com> wrote in message
>> news:31C5603C-AF2D-4C11-A79E-FC2EFA299165@microsoft.com...
>> >I am using excel as a scheduling software for construction. I have
>> >certain
>> > phases that may take 1 or more days. I am trying to type in the job
>> > number
>> > and have it return all the days each phase is scheduled on.
>> >
>> > I currently use the following Vlookup to find the first day:
>> > =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
>> > e5=Job Number
>> > b11=Phase Type
>> > Data!$E$1:$K$16200= the table_array I am pulling from.
>> > 4=the co-_index_num
>> >
>> > How can I have it return every match for this job number and phase
>> > type,
>> > with each match in a seperate row under each other?
>> > --
>> > Thanks,
>> >
>> > Ben
>>
>>
>>

If you will be inserting rows as a matter of routine you can build
functionality into the formula to compensate. I'm assuming you mean
inserting rows into the table array. This will work AS LONG AS THERE ARE NO
EMPTY ROWS WITHIN THE TABLE:

=IF(ISERROR(SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW(INDIRECT("$1:$"&COUNTA(Data!E:E)))),ROW(1:1))),"",INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW(INDIRECT("$1:$"&COUNTA(Data!E:E)))),ROW(1:1))))

The range Data!E$1:E$16200 should automatically adjust.

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:eKKpWdCXFHA.3176@TK2MSFTNGP12.phx.gbl...
>> How did I mess that up?
>
> By inserting new rows! <g>
>
> Make sure the range: Data!E$1:E$16200 and ROW($1:$16200) are the EXACT
> same size.
>
> Biff
>
> "Ben" <Ben@discussions.microsoft.com> wrote in message
> news:0CA65434-54F3-4FE0-B6EC-880288F9BB07@microsoft.com...
>> Biff this worked great until I inserted more rows since I hadn't recieved
>> a
>> #NUM yet. Now all the cells come up with #N/A.
>>
>> How did I mess that up?
>>
>> --
>> Thanks,
>>
>> Ben
>>
>>
>> "Biff" wrote:
>>
>>> Hi!
>>>
>>> Here's one way.
>>>
>>> Column 4 in your table array is column H.
>>>
>>> Entered with the key combo of CTRL,SHIFT,ENTER:
>>>
>>> =INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1)))
>>>
>>> Copy down until you get #NUM! errors meaning all matches have been
>>> found.
>>>
>>> If you don't want to see the #NUM! errors you could suppress their
>>> display
>>> by including an error trap in the formula. This makes the formula twice
>>> as
>>> long and since you're looking through a large range, may "slow" things
>>> down
>>> a bit. An alternative is to use conditional formatiing to "hide" the
>>> errors.
>>>
>>> Post back if you need further assistance in dealing with the #NUM!
>>> errors.
>>>
>>> Biff
>>>
>>> "Ben" <Ben@discussions.microsoft.com> wrote in message
>>> news:31C5603C-AF2D-4C11-A79E-FC2EFA299165@microsoft.com...
>>> >I am using excel as a scheduling software for construction. I have
>>> >certain
>>> > phases that may take 1 or more days. I am trying to type in the job
>>> > number
>>> > and have it return all the days each phase is scheduled on.
>>> >
>>> > I currently use the following Vlookup to find the first day:
>>> > =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
>>> > e5=Job Number
>>> > b11=Phase Type
>>> > Data!$E$1:$K$16200= the table_array I am pulling from.
>>> > 4=the co-_index_num
>>> >
>>> > How can I have it return every match for this job number and phase
>>> > type,
>>> > with each match in a seperate row under each other?
>>> > --
>>> > Thanks,
>>> >
>>> > Ben
>>>
>>>
>>>
>
>

Biff,

1. Great Tip Thanks! The problem isn't in the array it is in the lookup
list when I insert more rows because I haven't reached the #NA yet.

--
Thanks,

Ben


"Biff" wrote:

> If you will be inserting rows as a matter of routine you can build
> functionality into the formula to compensate. I'm assuming you mean
> inserting rows into the table array. This will work AS LONG AS THERE ARE NO
> EMPTY ROWS WITHIN THE TABLE:
>
> =IF(ISERROR(SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW(INDIRECT("$1:$"&COUNTA(Data!E:E)))),ROW(1:1))),"",INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW(INDIRECT("$1:$"&COUNTA(Data!E:E)))),ROW(1:1))))
>
> The range Data!E$1:E$16200 should automatically adjust.
>
> Biff
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:eKKpWdCXFHA.3176@TK2MSFTNGP12.phx.gbl...
> >> How did I mess that up?
> >
> > By inserting new rows! <g>
> >
> > Make sure the range: Data!E$1:E$16200 and ROW($1:$16200) are the EXACT
> > same size.
> >
> > Biff
> >
> > "Ben" <Ben@discussions.microsoft.com> wrote in message
> > news:0CA65434-54F3-4FE0-B6EC-880288F9BB07@microsoft.com...
> >> Biff this worked great until I inserted more rows since I hadn't recieved
> >> a
> >> #NUM yet. Now all the cells come up with #N/A.
> >>
> >> How did I mess that up?
> >>
> >> --
> >> Thanks,
> >>
> >> Ben
> >>
> >>
> >> "Biff" wrote:
> >>
> >>> Hi!
> >>>
> >>> Here's one way.
> >>>
> >>> Column 4 in your table array is column H.
> >>>
> >>> Entered with the key combo of CTRL,SHIFT,ENTER:
> >>>
> >>> =INDEX(Data!H:H,SMALL(IF(Data!E$1:E$16200=E$5&B$11,ROW($1:$16200)),ROW(1:1)))
> >>>
> >>> Copy down until you get #NUM! errors meaning all matches have been
> >>> found.
> >>>
> >>> If you don't want to see the #NUM! errors you could suppress their
> >>> display
> >>> by including an error trap in the formula. This makes the formula twice
> >>> as
> >>> long and since you're looking through a large range, may "slow" things
> >>> down
> >>> a bit. An alternative is to use conditional formatiing to "hide" the
> >>> errors.
> >>>
> >>> Post back if you need further assistance in dealing with the #NUM!
> >>> errors.
> >>>
> >>> Biff
> >>>
> >>> "Ben" <Ben@discussions.microsoft.com> wrote in message
> >>> news:31C5603C-AF2D-4C11-A79E-FC2EFA299165@microsoft.com...
> >>> >I am using excel as a scheduling software for construction. I have
> >>> >certain
> >>> > phases that may take 1 or more days. I am trying to type in the job
> >>> > number
> >>> > and have it return all the days each phase is scheduled on.
> >>> >
> >>> > I currently use the following Vlookup to find the first day:
> >>> > =VLOOKUP($E$5&$B11,Data!$E$1:$K$16200,4,FALSE)
> >>> > e5=Job Number
> >>> > b11=Phase Type
> >>> > Data!$E$1:$K$16200= the table_array I am pulling from.
> >>> > 4=the co-_index_num
> >>> >
> >>> > How can I have it return every match for this job number and phase
> >>> > type,
> >>> > with each match in a seperate row under each other?
> >>> > --
> >>> > Thanks,
> >>> >
> >>> > Ben
> >>>
> >>>
> >>>
> >
> >
>
>
>