Lookup Values which meet two or more criteria and return multiple matches horizontally

Lookup values which meet two or more criteria and return multiple matches horizontally.xlsxLookup values which meet two or more criteria and return multiple matches horizontally.PNGHi everyone

I have been reading your posts for a while and they have been very useful.

I was wondering if any of you can assist me with the following problem.

I would like to lookup for values (prices) in a table that meet two criteria (item name="Pen" & Delivery date<Today()) and retrieve all results horizontally. So far I have been able to identify two formulas that may help but I haven't been able to combine them and come with the solution yet.

The formulas are:

{=+IFERROR(INDEX($C$3:$C$12, SMALL(IF($A$3:$A$12="Pen",ROW($A$3:$A$12)-ROW($C$3)+1), COLUMN(A1))),"-")}
Main Issue: Retrieves values which do not meet the criteria


{=+IFERROR(INDEX($A$3:$C$12,MATCH(1,($A$3:$A$12="Pen")*($B$3:$B$12<TODAY()),0),3),"-")}
Main Issue: Retrieves only one of the values which meet the criteria

Have you come across this problem before?

Your help is greatly appreciated

Regards

Jose Martinez



Range to Lookup
Item Delivery Date Purchase Price Meet Criteria
Pen 05-Feb-2015 $5.20 No
Eraser 01-Feb-2015 $5.30 No
Paper 08-May-2015 $5.40 No
Pen 15-Jan-2015 $5.50 Yes
Paper clip 15-Jan-2015 $2.20 No
Pen 01-Feb-2015 $4.30 Yes
Eraser 05-Jan-2015 $2.10 No
Paper 31-Dec-2014 $6.30 No
Pen 30-Mar-2015 $3.20 No
Paper clip 05-Mar-2015 $9.50 No

Search Criteria
Item Pen
Delivery Date <Today() Today = 03-Feb-2015

Values to return (Horizontally)
Pen <Today() $5.50 $4.30
(15/01/2015) (1/02/2015)
Functions Used:

Formula: {=+IFERROR(INDEX($C$3:$C$12, SMALL(IF($A$3:$A$12="Pen",ROW($A$3:$A$12)-ROW($C$3)+1), COLUMN(A1))),"-")}
Results 5.2 5.5 4.3 3.2
Criteria Meet No Yes Yes No
Main Issue: Retrieves values which do not meet the criteria


Formula: {=+IFERROR(INDEX($A$3:$C$12,MATCH(1,($A$3:$A$12="Pen")*($B$3:$B$12<TODAY()),0),3),"-")}
Results 5.5 5.5 5.5 5.5
Criteria Meet Yes Yes Yes Yes
Main Issue: Retrieves only one of the values which meet the criteria

Can you upload a sample file? Saves having to retype what you posted.

I can't get Azumi's file to download.

Can't get your file to download either.

Try this...


=IFERROR(INDEX($C$3:$C$12,SMALL(IF(($A$3:$A$12="Pen")*($B$3:$B$12<TODAY()),ROW($A$3:$A$12)-ROW($A$3)+1), COLUMNS($A:A))),"-")


Drag it right...

Thank you everyone for your prompt response!

It's solved now.

Brilliant!

FlameRetired
I was able to download the file. I suspect it may be an issue with your computer.

Alan