none
min of indirect formula RRS feed

  • 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);"") ?

    Friday, August 29, 2014 9:08 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 xl-central.com - "For Your Microsoft Excel Solutions"

    Friday, August 29, 2014 1:58 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 xl-central.com - "For Your Microsoft Excel Solutions"


    Friday, August 29, 2014 10:24 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?



    Friday, August 29, 2014 1:33 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 xl-central.com - "For Your Microsoft Excel Solutions"

    Friday, August 29, 2014 1:58 PM
  • brilliant! What's what I need

    Thanks a lot

    Friday, August 29, 2014 2:18 PM
  • You're very welcome!

    Cheers!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Friday, August 29, 2014 2:40 PM