Vlookup Multiple Returns_Answers

[Guest]

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?

 

[Biff]

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

 

[Guest]

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

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

 

[Guest]

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

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

 

[Biff]

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

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!

 

[Biff]

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

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?

 

[Biff]

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

 

[Guest]

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.