Combining iferror, vlookup and min possible?

**dominoes** · 07-20-2012, 05:35 PM

I have been asked to find a minimum value out of a range of data linked to a name. However, some of the names I have been asked to find minimum values for are not in the database. Is it possible to combine an IFERROR into this formula =MIN(VLOOKUP(B2,".....","....",False))

Sorry if its a simple question, am very new to excel!

**JBeaucaire** · 07-20-2012, 06:56 PM

You can take any formula:

=MyWorkingAndAlreadyTestedFormula

...and wrap it inside an IFERROR():

=IFERROR(MyWorkingAndAlreadyTestedFormula, "")

**TMS** · 07-20-2012, 07:03 PM

VLOOKUP will only return the first value that matches the lookup value, B2. So you're not going to get a minimum regardless ... just an arbitrary value.

Regards, TMS

**ChuckYeager** · 07-20-2012, 07:55 PM

You can use an array formula.

=Min(if('LookupSheet'!$A$1:$A$10='ThisSheet'!A1,'LookupSheet'!$B$1:$B$10))

substitute
'LookupSheet'!$A$1:$A$10 with the Range in which the names are present (Should be a single column or row, with Absolute address)
'ThisSheet'!A1 with the Cell in which the name that you are looking is present
'LookupSheet'!$B$1:$B$10 with the Range in which you are looking for the minimum value. (Should be a single column or row, with Absolute address)

both
'LookupSheet'!$A$1:$A$10 and 'LookupSheet'!$B$1:$B$10 ranges should be of the same size.

Last and most important step after you write the formula... While still inside the cell where the formula is present, Press CTRL+SHIFT+ENTER to turn the formula into an Array Formula.

Drag the formula down to cover all names.

Should Work.

**dominoes** · 07-22-2012, 05:20 PM

Thank you all for the advice. ChuckYeager, that worked perfectly except some of the look up values should not be found in the data but the min value is coming back as 00:00:00 in time. Would there be a way to get rid of this at all? So that the formula acknowledge that the lookup value hasnt been found?
Thanks!

**ChuckYeager** · 07-22-2012, 06:54 PM

=IF(COUNTIF('LookupSheet'!$A$1:$A$10,'ThisSheet'!A1)<>0,Min(if('LookupSheet'!$A$1:$A$10='ThisSheet'!A1,'LookupSheet'!$B$1:$B$10)),"NA")

What I have done is - added a COUNTIF() around the earlier formula which would check and if the lookup value is present in the reference table and return "NA", if the Lookup Value is not present else return MIN Value as per the earlier formula (in RED).

**Haseeb Avarakkan** · 07-22-2012, 07:00 PM

You could also use SMALL,

=IFERROR(SMALL(if('LookupSheet'!$A$1:$A$10='ThisSheet'!A1,'LookupSheet'!$B$1:$B$10),1),"N/A")

If lookup_value hasn't been found will return an error, so IFERROR will make it "N/A"