Extract data based on criteria

[maniknandi]

Hello Everyone,

I want to extract data from a table, Where I select an item, it will extract the available place and amount. Attached kindly find the file.

Many many thanks in advance.

 

[vletm]

maniknandi
One possible solution...

 

[maniknandi]

This is Working fine. Thank you so much. Do you have solution other than VBA?

 

[vletm]

maniknandi
Sorry no,
... it would be challenge for me to show needed borders and colors without VBA.

 

[maniknandi]

Without borders and colors?

 

[bosco_yip]

Formula solution with Conditional Formatting for coloring cells and border.

1] In B21, criteria enter : "Manago", "Apple", "Banana" and "Guava"

2] In D22, copied down to D26 :

=IFERROR(INDEX(C$3:G$3,AGGREGATE(15,6,COLUMN(A$1:E$1)/(INDEX(C$4:G$7,MATCH(B$21,B$4:B$7,0),0)>0),ROW(A1))),"")

3] In E22, copied down to E26 :

=IFERROR(INDEX(C$4:G$7,MATCH(B$21,B$4:B$7,0),AGGREGATE(15,6,COLUMN(A1:E1)/(INDEX(C$4:G$7,MATCH(B$21,B$4:B$7,0),0)>0),ROW(A1))),"")

4] Conditional formatting for coloring cells and border to D22:D26 and E22:E26, please see :



Regards
Bosco

 

[bosco_yip]

Please see attachment of the above post

Regards

 

[Peter Bartholomew]

I am not totally happy with my solution (it uses INDIRECT and lacks scalability)
Now to take a look at Bosco's

 

[vletm]

maniknandi
Your: Without borders and colors?
You asked with those parameters...
( but You can add as many row 3 criteria as You needs )

Seems that others have something else for You.

 

[Peter Bartholomew]

I was trying to output the arrays of up to 5 values using a single array formula and failed using CSE though it worked with Dynamic Arrays.
Bosco's strategy of outputting each value independently would seem to sort the problem.
I have arranged things so that multiple copies of the dialogue will work if it copied and pasted but that may not be required.