**Question description:**

*This user has given permission to use the problem statement for this blog.*

I am having issues with max If function array formula using Is number / Find to find data

Solved by D. C. in 22 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
06/10/2017 - 10:50

Hi there. I understand that you need help with the Max IF array formula. Are you able to upload your data and your existing formula so I can analyze it?

User
06/10/2017 - 10:50

hello

User
06/10/2017 - 10:50

i have a file

User
06/10/2017 - 10:50

can i upload

Excelchat Expert
06/10/2017 - 10:51

Sure. Please upload using the paper clip icon next to this chat.

User
06/10/2017 - 10:51

i have xlsm file

User
06/10/2017 - 10:51

it doesnt seem to be accepted

User
06/10/2017 - 10:51

upload format

Excelchat Expert
06/10/2017 - 10:51

I see. Please upload in Google Drive instead.

User
06/10/2017 - 10:52

which button do i press for that

Excelchat Expert
06/10/2017 - 10:52

Please go to https://drive.google.com Click New > File Upload. Navigate to the file and select it to upload. Click Recent. Right click on the file. Click Share. Click Get Shareable Link. Make sure "Anyone with the link can view" is selected. Click Copy Link. Then paste the link here so I can download it.

Excelchat Expert
06/10/2017 - 10:52

Please be advised that we have a policy of 1 question per session and for this session we will be working on your Max If array formula function.

User
06/10/2017 - 10:52

ok sure

User
06/10/2017 - 10:54

https://drive.google.com/open?id=0B4WvKmxpOE2iR2VpSk1jZjFKT0E

User
06/10/2017 - 10:54

cell L28

User
06/10/2017 - 10:55

sheet 3BAI-20

Excelchat Expert
06/10/2017 - 10:55

Which sheet.

Excelchat Expert
06/10/2017 - 10:55

Okay, do you have named ranges here?

User
06/10/2017 - 10:56

yes

User
06/10/2017 - 10:56

let me reload file

Excelchat Expert
06/10/2017 - 10:56

Okay, that can complicate it a bit as we'll have to follow them too.

User
06/10/2017 - 10:56

P6 data page was slight wrong

User
06/10/2017 - 10:56

i manually changed something

Excelchat Expert
06/10/2017 - 10:56

Okay, sure. Please note that we have a limited amount of time for this and we have to act quickly soon.

User
06/10/2017 - 10:57

https://drive.google.com/open?id=0B4WvKmxpOE2iell4VWtJNjFNVlE

User
06/10/2017 - 10:57

ok

User
06/10/2017 - 10:57

ready

User
06/10/2017 - 10:57

3BAI-20 L28

User
06/10/2017 - 10:58

If min array formula should return 3/10/17

User
06/10/2017 - 10:58

based on Min IF

User
06/10/2017 - 10:59

it doesnt seem to like numbers at end

Excelchat Expert
06/10/2017 - 10:59

Did you hide the first columns in p6data?

Excelchat Expert
06/10/2017 - 10:59

Because I'm trying to follow your P6_SamID

User
06/10/2017 - 11:00

freeze panes

Excelchat Expert
06/10/2017 - 11:00

P6_SamID = A:A

User
06/10/2017 - 11:00

yes

User
06/10/2017 - 11:01

problem is if i change P6_DAGlobal name range to "DA" it works

User
06/10/2017 - 11:01

if I put DA01

User
06/10/2017 - 11:01

DA02 doesnt work

User
06/10/2017 - 11:01

I want it work like a wildcard

User
06/10/2017 - 11:01

thats why I am using the ISNUMBER(FIND) function

Excelchat Expert
06/10/2017 - 11:01

I need to understand how your formula works first before I can give you my recommendations. I'll need time to disect it as the named ranges aren't very clear.

User
06/10/2017 - 11:02

ok thanks

Excelchat Expert
06/10/2017 - 11:02

Please give me a few minutes.

Excelchat Expert
06/10/2017 - 11:02

P6_AccuralNC = AG:AG

User
06/10/2017 - 11:03

basically there is 3 criteria

User
06/10/2017 - 11:03

IF(P6_SamID=$D$6,IF(P6_AccuralNC=$B28,IF(ISNUMBER(FIND($A28,P6_DAGlobal))

User
06/10/2017 - 11:03

only one not working is last one

User
06/10/2017 - 11:03

IF(ISNUMBER(FIND($A28,P6_DAGlobal))

Excelchat Expert
06/10/2017 - 11:04

So basically, it has to meet all 3 criteria.

User
06/10/2017 - 11:04

yes

Excelchat Expert
06/10/2017 - 11:04

And then return the minimum.

User
06/10/2017 - 11:04

yes

User
06/10/2017 - 11:04

but it doesnt like that i got DA01 DA02 etc

User
06/10/2017 - 11:05

I want A28 to work as wildcard

Excelchat Expert
06/10/2017 - 11:05

What's the expected value of L28

User
06/10/2017 - 11:05

thats why i am using the find function

User
06/10/2017 - 11:05

3/10/17

User
06/10/2017 - 11:05

3/10/17

User
06/10/2017 - 11:05

O36 P6 Data

User
06/10/2017 - 11:05

is correct MIN value

Excelchat Expert
06/10/2017 - 11:06

I see. One moment.

Excelchat Expert
06/10/2017 - 11:06

If you are prompted to extend, please extend the session.

User
06/10/2017 - 11:06

ok

Excelchat Expert
06/10/2017 - 11:09

Okay, I have to rewrite your array formula:

Excelchat Expert
06/10/2017 - 11:09

Please try this:

Excelchat Expert
06/10/2017 - 11:09

=MIN(IF((P6_SamID=$D$6)*(P6_AccuralNC=$B28)*(ISNUMBER(FIND($A28,P6_DAGlobal))),VALUE(LEFT(P6_Start,9))))

User
06/10/2017 - 11:10

ok perfect seems to be working

User
06/10/2017 - 11:10

how does that work vs mine not working

Excelchat Expert
06/10/2017 - 11:11

Basically, I just use your same formula, the only difference is the formatting. Yours may be calculating it in an incorrect order.

User
06/10/2017 - 11:11

u are using the *

User
06/10/2017 - 11:11

thats the key difference

Excelchat Expert
06/10/2017 - 11:11

I wasn't really able to fully analyze it as I'm not familiar with how you constructed your array formulas.

User
06/10/2017 - 11:11

ok great

User
06/10/2017 - 11:11

regardless working

User
06/10/2017 - 11:11

can u explain how i can reach you on here in future

User
06/10/2017 - 11:11

for other excel help

Excelchat Expert
06/10/2017 - 11:12

I'm afraid you will just have to post your question and the right expert will pick you up.

User
06/10/2017 - 11:12

ok

User
06/10/2017 - 11:12

thanks

Excelchat Expert
06/10/2017 - 11:12

Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)

Excelchat Expert
06/10/2017 - 11:12

Please do not forget to click the End Session otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*