• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find value in a range

Max Alejandro Molina

Max Alejandro Molina

Member
Hello Excel Ninjas,

I wanna find any value in cells:

S3, Y3, AE3, AK3, AQ3, AW3, BC3, BI3, BO3, BU3, CA3, CG3, CM3, CS3, CY3,

and then have tha value returned to cell M3, I have already tried this:

=SI(S3>0;S3;SI(Y3>0;Y3;SI(AE3>0;AE3;SI(AK3>0;AK3;SI(AQ3>0;AQ3;SI(AW3>0;AW3;SI(BC3>0;BC3;SI(BI3>0;BI3;SI(BO3>0;BO3;SI(BU3>0;BU3;SI(CA3>0;CA3;SI(CG3>0;CG3;SI(CM3>0;CM3;SI(CS3>0;CS3;SI(CY3>0;CY3;"No encontrado")))))))))))))))

But it does not work,

Can anybody help me?
 
Hi Max,

A blind shot :) Try below array formula:

=INDEX(S3:CY3,,MAX(IF((S3:CY3>0)*ISNUMBER(S3:CY3),COLUMN(S3:CY3)-COLUMN(S3)+1)))

Well you posted your formula in a language which most of us will not understand and did not make clear what will be the cell contents.

Regards,
 
Somendra Misra said:
Hi Max,

A blind shot :) Try below array formula:

=INDEX(S3:CY3,,MAX(IF((S3:CY3>0)*ISNUMBER(S3:CY3),COLUMN(S3:CY3)-COLUMN(S3)+1)))

Well you posted your formula in a language which most of us will not understand and did not make clear what will be the cell contents.

Regards,
Click to expand...

Somandra i was just about to repost this lool, but SI is the IF function and the thing is that the values are dates, and there will not be more than 1 value in all those cells.
 
Somandra it has to be specifically in those cells, not in the whoel range .

The cells are:
Somendra Misra said:
Than you can try this also: enter with Ctrl+Shift+Enter

MAX(IF((S3:CY3>0)*ISNUMBER(S3:CY3),S3:CY3))

Or simply

=SUMPRODUCT((S3:CY3>0)*ISNUMBER(S3:CY3),S3:CY3)
with Enter

Regards,
Click to expand...

Somandra it has to be specifically in those cells, not in the whole range .

The cells are:

S3, Y3, AE3, AK3, AQ3, AW3, BC3, BI3, BO3, BU3, CA3, CG3, CM3, CS3, CY3

The reason why it has to be in these cells is because i will have other values in cell for instance CB3 and i dont want the function to take the cells that arent mentioned into account, did i explain well enough?
 
Somendra, please see the file. I want to be able to drag down the selected range and to have all records in order in just columns M:Q. Let me know if further doubts.
 

Attachments

  • Maqueta Proyecto (2).xlsx
    536.3 KB · Views: 1
Why doesnt this formula work:

=IF(S3>0;S3;IF(Y3>0;Y3;IF(AE3>0;AE3;IF(AK3>0;AK3;IF(AQ3>0;AQ3;IF(AW3>0;AW3;IF(BC3>0;BC3;IF(BI3>0;BI3;IF(BO3>0;BO3;IF(BU3>0;BU3;IF(CA3>0;CA3;IF(CG3>0;CG3;IF(CM3>0;CM3;IF(CS3>0;CS3;IF(CY3>0;CY3;"No encontrado")))))))))))))))
 
Somendra Misra said:
First your S3 is "" which is > 0 So it displays value of S3 i.e. "". Second you had used 15 Nested IF, which I think beyond Nested If that excel can handle.

Regards,
Click to expand...
Do you have an idea how i can work around this problem? I just want to consolidate the info from the right columns into single columns( the ones in red)

Please see my example of how i wanted to end looking like. The example in red, which is taking the info from the right columns.
 

Attachments

  • Hta_Inventariosformula.xlsx
    544.2 KB · Views: 1
Somendra, sorry i keep bugging you.. I have a little situation ragarding this project you have been helping me to pull off.. as you know the large functions takes the larger value, but now i dont want the largest value, i want the last value, can this be possible?
 

Attachments

  • Formulas.xlsx
    15.5 KB · Views: 3
Somendra Misra said:
Hi Max,

Welcome back. I did not got anything from the file. Can you explain your requirement in a more detail.

Regards,
Click to expand...

If you try this array formula in G5 : =IFERROR(LARGE(SI($O$6:$O$32=E6,IF(INDEX($R$6:$AB$32,,MATCH(A6,$R$5:$AB$5,0))<>0,$AD$6:$AH$32)),1),"")

It will give you 30/07/2014 because that is the largest date related with that item/Distibution Center, but i dont want the largest date i want the last value related to that item in this case if you do it manually would be 25/07/2014,

Can this be done?
 
Try below array formula in G6 and copy down with Ctrl+Shift+Enter.

Code: 
=LARGE(INDEX(COUNTIF(E6,$O$6:$O$32)*IF(INDEX($R$6:$AB$32,,MATCH(A6,$R$5:$AB$5,0))<>0,$AD$6:$AH$32,0),LARGE(IF(COUNTIF(E6,$O$6:$O$32)*IF(INDEX($R$6:$AB$32,,MATCH(A6,$R$5:$AB$5,0))<>0,$AD$6:$AH$32,0)>0,ROW($O$6:$O$32)-ROW($O$6)+1),1),),1)
Regards,
 
Last edited:
Somendra Misra said:
Try below array formula in G6 and copy down with Ctrl+Shift+Enter.

Code: 
=LARGE(INDEX(COUNTIF(E6,$O$6:$O$32)*IF(INDEX($R$6:$AB$32,,MATCH(A6,$R$5:$AB$5,0))<>0,$AD$6:$AH$32,0),LARGE(IF(COUNTIF(E6,$O$6:$O$32)*IF(INDEX($R$6:$AB$32,,MATCH(A6,$R$5:$AB$5,0))<>0,$AD$6:$AH$32,0)>0,ROW($O$6:$O$32)-ROW($O$6)+1),1),),1)
Regards,
Click to expand...
Somendra,

It did not work, idont know if im entering it in a wrong way, can you upload file?
 
Hi Somendra,

It works perfectly as it is, but i am linking the formula to other workbook and it does not work. What limitions do i have, can i even use another workbook to get the info?
 
upload_2014-8-7_11-40-52.pngHere`s how the fomrula looks, so the size wouldnt affect, correct? and i have checked this ten times i dont understand why it does not work :O.... any tips?
 
You must log in or register to reply here.
Back
Top