Alternative Formula

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test following Array Formula :

Code:
=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

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
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

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
Hi James all good now thanks, just added the Data part of the ranges

Thanks again
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Oct 2, 2006
Messages
2,341
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

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

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top