how to return mulitple corresponding values

[Chris]

Awesome, I have been searching for this... How do i mod it for to return a -
etc if there is an error. Such as it has checked the list and returned
everything but i have a defined range.

 

[T. Valko]

How about providing some specific details about what you're wanting to do.

 

[Chris]

When I drag it down and fill in the cells, i get #NUM as it cannot locate any
more matches. How do i ISERROR that out to return a "-" after it meets the
end threshold.

 

[T. Valko]

The generic method is like this:

=IF(ISERROR(SMALL(IF(A$1:A$10=lookup_value,ROW($1:$10)),ROW(1:1))),"",INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW($1:$10)),ROW(1:1))))

However, that's not very efficient or robust. If you provide some details we
can come up with something that's better.

 

[eec]

is there a way to modify this formula to match 2 values and return multiple
corresponding values? i need column a = x and column b = y and then return
results.

 

[T. Valko]

i need column a = x and column b = y and then return results.

Return what results? From where?

 

[eec]

sorry for the lack of detail. i am pulling info from one worksheet with all
of our invoice information and trying to make a report that only shows the
invoices that match 2 criteria. so if a row of data contains both x and y
then i want it to show on the report. which for now is just a separate tab
in the same workbook. from the previous posts in this string i can pull data
that matches one criteria i just don't know how or if i can expand it to
match two criteria. thanks

 

[T. Valko]

Here's a small sample file that demonstrates this.

EEC.xls 19kb

http://cjoint.com/?cdr7UrLcDS

 

[eec]

you are my hero. thanks so much for the help!!

Here's a small sample file that demonstrates this.

EEC.xls 19kb

http://cjoint.com/?cdr7UrLcDS

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!