Cell format causing formula to return nothing

[ExcelRoy]

Hi,

I have the following formula

=IFERROR(LOOKUP(2,1/(Internal!$D$12:$D$5000=C16)/(Internal!$F$12:$F$5000=E16),(Internal!$G$12:$G$5000)),"")

If E16 = alpha numeric values the desired data is returned from G16

but if E16 is numbers only it returns nothing from G16

I have tried changing the column formats to match etc but no luck

Any help appreciated

Thanks

 

[TDCox]

Hi @ExcelRoy

What is "Internal" defined as? Is it the sheet that the formula is on? Could you provide an example of the data you're looking up please? The reason it's returning nothing is because your IFERROR statement is picking up the errors and returning "". I'm unsure what you're trying to do with the division dividers in the formula. Is it possible you could use a vlookup or hlookup instead?

 

[TDCox]

Hi @ExcelRoy

I presume you're using the lookup to lookup the latest value from an array (such as outlined here https://exceljet.net/formula/lookup-latest-price). The formula is looking at both C16 and E16 and that may be where one of your issues lies; you could encapsulate a cast within e.g. "str(Internal!$F$12:$F$5000)=str(E16)" which casts both the lookup and the equals as strings. The reason you're getting nothing is because that's what the IFERROR is set to return if the formula returns an error.