Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Lookup Two Values In A List And Return Multiple Corresponding Values

1. I agreed to these rules
Join Date
26th March 2008
Posts
7

## Lookup Two Values In A List And Return Multiple Corresponding Values

I know how to look up one value in a list and return multiple corresponding values, but I don't know how to change the formula to two values.

My current code is:

=IF(ISERROR(INDEX(\$A\$1:\$K\$21,SMALL(IF(\$K\$1:\$K\$21="QUO",ROW(\$K\$1:\$K\$21)),ROW(1:1)),5)),"",INDEX(\$A\$1:\$K\$21,SMALL(IF(\$K\$1:\$K\$21="QUO",ROW(\$K\$1:\$K\$21)),ROW(1:1)),5))

I want it to also look up "Y" in column I; \$I\$1:\$I\$21="Y"

Thanks.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup Two Values In A List And Return Multiple Corresponding Values

Welcome to board!!

In a spare cell(suppose L1),

=SUMPRODUCT(--(\$K\$1:\$K\$21="QUO"),--(\$I\$1:\$I\$21="Y"))

=IF(ROWS(\$M\$1:\$M1)<=\$L\$1,INDEX(\$E\$1:\$E\$21,SMALL(IF(\$K\$1:\$K\$21="QUO",IF(\$I\$1:\$I\$21="Y",(ROW(\$K\$1:\$K\$21))),ROWS(\$M\$1:\$M1))),"")

Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

HTH

3. I agreed to these rules
Join Date
26th March 2008
Posts
7

## Re: Lookup Two Values In A List And Return Multiple Corresponding Values

Hi Krishna,

Thanks for the welcome.

I've tried the code and the first bit seamed to work it returned how many cases match my criteria.

When I tried the second part I got a error message telling me that; 'You've entered too few argement for this function'

Any ider how to fix it?

Many thanks

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup Two Values In A List And Return Multiple Corresponding Values

Hi,

It was an untested formula. There was an extra "(" before the ROW part.

Try this

=IF(ROWS(\$M\$1:\$M1)<=\$L\$1,INDEX(\$E\$1:\$E\$21,SMALL(IF(\$K\$1:\$K\$21="QUO",IF(\$I\$1:\$I\$21="Y",ROW(\$K\$1:\$K\$21))),ROWS(\$M\$1:\$M1))),"")

HTH

5. I agreed to these rules
Join Date
26th March 2008
Posts
7

## Re: Lookup Two Values In A List And Return Multiple Corresponding Values

It's now returning the first value that matches the criteria in column K but not the second or third.

It repeats the first value.
Auto Merged Post Until 24 Hrs Passes;

It works, I have repeated data in the columns and I did not realise.

Thanks so much for you help Kris.
Last edited by MadeleineB; April 2nd, 2008 at 19:16. Reason: Auto Merged Doublepost

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup Two Values In A List And Return Multiple Corresponding Values

It should be dragged down. Not across.
Anyway can you attach the workbook?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno