Alternative Formula

[ExcelRoy]

Good evening,

Would it be possible to have an alternative formula to the following as this only returns data if D12 is a alpha numeric value

=IFERROR(LOOKUP(2,1/(Data!$AK$3:$AK$502=D12)/(Data!$AD$3:$AD$502=F12),(Data!$X$3:$X$502)),"")

this matched D12 within AK3:AK502 and F12 within AD3:AD502 and returns from column X in that row

Many thanks

 

[James006]

Hello,

You could test following Array Formula :

=INDEX((Data!$X$3:$X$502),LARGE(IF(ISNUMBER(SEARCH(D12,$AK$3:$AK$502)),IF(ISNUMBER(SEARCH(F12,$AD$3:$AD$502)),ROW($X$3:$X$502)),""),1))

Hope this will help

 

[ExcelRoy]

Hi James,

many thanks for the reply

It appears there is a #NUM error for your formula?

I am running 2007 if that makes any difference

Thanks

 

[ExcelRoy]

Hi James all good now thanks, just added the Data part of the ranges

Thanks again

 

[James006]

Hi again,

Quite honestly ... building a formula groundless ... without the visual support of the worksheet can be tricky ...

We do agree this is an Array Formula ... where you are using simultaneously the three keys : Control Shift Enter .... instead of the standard Enter key ...

 

[ExcelRoy]

Hi James,

Absolutely, must be a mind reading exercise most of the time

Just looking a little closer it appears that the formula doesn't return the correct match data? but it does return something in the desired column

Thanks

 

[James006]

Hello,

Can you ... at least ... post your latest formula ... :wink: