Lookup issue returning odd results

[ExcelRoy]

Hi all,

I have the following LOOKUP formula

=IF(C3&I3="","",IFERROR(LOOKUP(9.99999999999999E+307,1/(($FL$3:$FL$10002=C3)*($FM$3:$FM$10002=I3)),$FO$3:$FO$10002),"Backorder"))

This seems to work most of the time but for some reason results "" when there is nothing found in FO3:FO10002
Sometimes it shows 00/01/1900 also

Is there a way to modify the formula to only show "Backorder" if nothing is found in FO3:FO10002

But retaining the =IF(C3&I3="","" part of the formula

Many thanks for any help

 

[DanteAmor]

It shows 00/01/1900Because in the cell you have a date format, the formula returns a 0 and then excel converts the 0 into an excel date.

Then you have to duplicate the formula, if the result is 0 then "do not date" otherwise use the formula again.
Try with the following array formula

{=IF(IFERROR(INDEX($FO$2:$FO$1000,MAX(($FL$3:$FL$1000=C3)*($FM$3:$FM$1000=I3)*ROW($FO$3:$FO$1000))-1),"Backorder")=0,"No data",IFERROR(INDEX($FO$2:$FO$1000,MAX(($FL$3:$FL$1000=C3)*($FM$3:$FM$1000=I3)*ROW($FO$3:$FO$1000))-1),"Backorder"))} <tbody> </tbody>

<tbody>
</tbody>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[RoryA]

Perhaps:

=IF(C3&I3="","", IFERROR(1/(1/LOOKUP(9.99999999999999E+307,1/(($FL$3:$FL$10002=C3)*($FM$3:$FM$10002=I3)),$FO$3:$FO$10002)),"Backorder"))

 

[ExcelRoy]

Hi DanteAmor, sorry but I have the same issues with your formula

Hi RoryA, I thought this was working perfectly but it doesn't pick up any data from FO3:FO10002

Thanks

 

[RoryA]

What exactly is in column FO - i.e. what type of data?

 

[ExcelRoy]

Hi RoryA,

Generally text or nothing?

So the desired result would be to return the general text but if nothing found then return "Backorder"

Thanks

 

[RoryA]

Ah, I had assumed dates. In that case try:

=IF(C3&I3="","",IFERROR(LOOKUP(9.99999999999999E+307,1/(($FL$3:$FL$10002=C3)*($FM$3:$FM$10002=I3)),$FO$3:$FO$10002)&"","Backorder"))

 

[ExcelRoy]

Hi RoryA,

Thanks, I think I might know the problem (not sure)

FO3:FO10002 wont always have anything to return, so if the match from FL and FM is made the formula will return "" if there is nothing in FO

FO is an order status, FL and FM are alpha numeric part/job numbers

So to summarize if the match is made and there is nothing in FO then return "Backorder"

Hope that makes sense

Thanks for your help

 

[RoryA]

OK, you can add that as another condition then:

=IF(C3&I3="","",IFERROR(LOOKUP(9.99999999999999E+307,1/(($FL$3:$FL$10002=C3)*($FM$3:$FM$10002=I3)*($FO$3:$FO$10002<>"")),$FO$3:$FO$10002),"Backorder"))

 

[DanteAmor]

Hi DanteAmor, sorry but I have the same issues with your formula

Hi RoryA, I thought this was working perfectly but it doesn't pick up any data from FO3:FO10002

Thanks

If you have this:

Generally text or nothing?

Try:

{=IF(IFERROR(INDEX($FO$2:$FO$1000,MAX(($FL$3:$FL$1000=C3)*($FM$3:$FM$1000=I3)*ROW($FO$3:$FO$1000))-1),"Backorder")="","No data",IFERROR(INDEX($FO$2:$FO$1000,MAX(($FL$3:$FL$1000=C3)*($FM$3:$FM$1000=I3)*ROW($FO$3:$FO$1000))-1),"Backorder"))}