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

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