Answered by:
min of indirect formula
Question

HI ALL
Can anyone knows how to fix the formula below to get min value as a result?
Let's explain. I have a Database sheet where I collect data from other sheets. The other sheets are named: TX L39BL6, 47ASE650E, 42AS500E ... (up to 150 sheets). Those names represent names of TV models. These sheets are web queries so I can not modify them. I wrote formula to look up data form those sheets to my database sheet. This formula is written below and is working well. But after this I found that some stores has repeated names (AGD Market Sp. z o.o.). In those cases this formula do not get what I need. For example: for the model TX L39 BL6 I got a price 999 (first price represent AGD Market Sp. z o.o.). in a sheet TX L39BL6) I expect to receive 899 (min price for AGD Market Sp. z o.o.). in a sheet TX L39BL6).
Any idea how to fix it =IFERROR(VLOOKUP($A1;INDIRECT("'"&B1&"'!"&"$A$1:$B$10000");2;FALSE);"") ?
 Edited by Bartek Wachocki Friday, August 29, 2014 9:16 AM
Answers

Try...
=IFERROR(SMALL(IF(INDIRECT("'"&B1&"'!$A$1:$A$10000")=$A1,IF(INDIRECT("'"&B1&"'!$B$1:$B$10000")>0,INDIRECT("'"&B1&"'!$B$1:$B$10000"))),1),"")
...confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"
 Marked as answer by Bartek Wachocki Friday, August 29, 2014 2:18 PM
All replies

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...
=IFERROR(SMALL(IF(INDIRECT("'"&B1&"'!$A$1:$A$10000")=$A1,INDIRECT("'"&B1&"'!$B$1:$B$10000")),1),"")
Or, for your version of Excel, try the following formula that also needs to be confirmed with CONTROL+SHIFT+ENTER...
=IFERROR(SMALL(IF(INDIRECT("'"&B1&"'!$A$1:$A$10000")=$A1;INDIRECT("'"&B1&"'!$B$1:$B$10000"));1);"")
Adjust the ranges, accordingly.
Hope this helps!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"
 Edited by Domenic Tamburino Friday, August 29, 2014 10:25 AM

Thanks a lot Domenic
Works well, but I have found some exceptions which needs to be fixed.
What if the data in one of the reference sheets contains blanks or zero?
I would like to omit those cases. Example:
The expected result is 1399
How the formula wiil look like after this change?
 Edited by Bartek Wachocki Friday, August 29, 2014 1:37 PM

Try...
=IFERROR(SMALL(IF(INDIRECT("'"&B1&"'!$A$1:$A$10000")=$A1,IF(INDIRECT("'"&B1&"'!$B$1:$B$10000")>0,INDIRECT("'"&B1&"'!$B$1:$B$10000"))),1),"")
...confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"
 Marked as answer by Bartek Wachocki Friday, August 29, 2014 2:18 PM

