Vlookup, MAX and IF formula

[Ted Dennis]

In a nutshell I am trying the write a forumula that will identify the highest number for a range of data.

Basicaly I need to identify the highest number for a particular person, however the data I have will contain more than 1 person so as I see it I need to identify using an IF to determin if col a = Mr A then look at col b to see which is the highest number and identify that as the highest in col c

Hope this makes sense.

Thanks

Ted

[Fotis1991]

Hi Ted

Try

=MAX(IF(A1:A100="Mr A",b1:b100))

Usually you need to use this formula as an ARRAY...




-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[Ted Dennis]

I have amend the forumlae to =MAX(IF(A2:A1650=A2,BO2:BO1650)) as 'Mr A' is in col A and the range where i want to identify the max number is in col BO, however I am returning ther Max for the whole data range and not just for the specified "Mr A"

Thanks

[ramananhrm]

Please attached a sample file & your desired result.

[Fotis1991]

In A2 is "Mr A" ???

If YES, just lock the formula....

=MAX(IF($A$2:$A$1650=$A$2,$BO$2:$BO$1650))

If not try in this way..

=MAX(IF($A$2:$A$1650=A2,$BO$2:$BO$1650))

OR else pls upload a small sample workbook.



To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Ted Dennis]

Unfortunately I am unable to upload a file i keep getting a error probably my work sytem not allowing it. The formulae above work in exactly the same way as previously.

The problem is that I have within the range of data Mr A, B C D and E the formula suggested is only finding the max number which applies across all Mr A, B C D and E and not just Mr A.

Thanks

[Fotis1991]

..the formula suggested is only finding the max number which applies across all Mr A, B C D and E and not just Mr A.

Ted. Do you confirm the formula as an ARRAY formula as i suggested?

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

Pls test it as an ARRAY formula and let me know if you need to upload a sample for you!

[Ted Dennis]

Sorry, my fault I have now entered as an Array formula and it has worked.

Thanks for your help

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

[Ted Dennis]

Sorry I have a further compliacation linked to this

I think I need to apply the same type of array formula. I have now got a pivot table with my list of names Mr A to E, i need to do a look up from this pivot back to my original data. Efectively this will be an if to start with which is not a problem my problem arisies becasue I need to lookup the a column ( where Mr is and return a reference number which sits in col d ) but I need to fulfil 2 other conditions first where col e sates "exclude" and out of the rows flagged as exclude I need to find the max number in col f

Hope this follows

Thanks again

[Fotis1991]

Sorry Ted but i need a sample worksheet for this. If you have problem to do this from your work, do it from your home in the afternoon and i'll take a look there.

[Ted Dennis]

Thanks probably won't be for another 7 hrs I will mock up a TEST sheet and upload it

[Fotis1991]

i'll be waiting..

[Ted Dennis]

I have had a thought and tried to do this my self can I combine an "AND within the existing formula e.g.

=MAX(IF(AND($A$2:$A$1650=A2,$BT$2:$BT$1650="exclude"),$BO$2:$BO$1650))

Effectively return MAX on the condition that A2 is the same and BT = exclude ?

[Fotis1991]

You can use many condition for MAX(IF without use the AND function..

=MAX(IF($A$2:$A$1650=A2,IF($BT$2:$BT$1650="exclude",$BO$2:$BO$1650)))

[Ted Dennis]

test.xlsx

This the upload.

Effectively If in the pivot the number of checks =0 I need to find a case that has been excluded aND IS THE HIGHEST SCORING FOR THAT PERSON

Thanks again

Ted

[Fotis1991]

Hoping that i got your goal, give a try to this ARRAY function.

=IF(SUMPRODUCT(($A$2:$A$14=$G6)*($B$2:$B$14=$F$1));"";MAX(IF($A$2:$A$14=$G6;IF($B$2:$B$14=$G$1;$D$2:$D$14))))

Comments?

[Ted Dennis]

sorry I also need to return the ref no. in col c i.e for the example of 64 i would need to retrun a0006

Thanks again

[Ted Dennis]

Hi I have transposed what you have done to the actual sheet it now looks like this

=IF(SUMPRODUCT(('July Scores'!$A$2:$A$1650=$A5)*('July Scores'!$BV$2:$BV$1650=$R$2)),"",MAX(IF('July Scores'!$A$2:$A$1650=$A$3,IF('July Scores'!$BV$2:$BV$1650=$R$3,'July Scores'!$BY$2:$BY$1650))))

Something is working because where I have "checked cases" (i.e. a number in the check field) the field is blank as per your example, where there is a no cases checked i am returning 0 on all Mr's

I have entered the forumal as an array

Thanks

[Ted Dennis]

Ok, I used the formula on the same sheet which is the "july Sheet" ststed in my formula in the post above and this now works, I'm not sure why this doesn't work accross sheets is this something to do with the fact that this is an array formula. I would however still like to return effectively col c.

Thanks again

[Fotis1991]

So Ted.

In I6 of my example, put this ARRAY formula...

=INDEX($C$2:$C$14,MATCH(IF(SUMPRODUCT(($A$2:$A$14=$G6)*($B$2:$B$14=$F$1)),"",MAX(IF($A$2:$A$14=$G6,IF($B$2:$B$14=$G$1;$D$2:$D$14)))),$D$2:$D$14,0))

This will gives you "a0006" as result.

[Ted Dennis]

ok sorted it now, need to remeber the ctrl shift enter. As i said previously can i use the array forula across to sheets ?

[Fotis1991]

ok sorted it now, need to remeber the ctrl shift enter. As i said previously can i use the array forula across to sheets ?

What to you mean? Some data in one sheet and some others to another sheet? YES you can.

[Ted Dennis]

I have done this but its returning a #n/a.

any ideas

[Ted Dennis]

Obviously the initial Test was on 1 sheet, however when I tested it (see post above refering to 'July Scores' sheet) I was using 2 worksheets this didn't work I don't know why as there was no error it was just returning a 0 ? When I used the same as you did on one sheet it worked fine ? The other problem iis the Index function that you added fro me, this is not working, becasue where there is a repeat number in col D it keeps picking that up?

Thanks again for your assistance

Cheers

Ted

[Fotis1991]

Ted

You give me i by 1 new info and 1 day after the other(i mean we started at 28/08/2013 and now is 30/08/2013)..
and without to show me some examples...You told:

..sorry I also need to return the ref no. in col c i.e for the example of 64 i would need to retrun a0006

You didn't told that maybe will be many results....

I like to continue to help you but:

As i don't want to make 50 posts here, pls:

Make a sample workbook to be exactly as your original. Ask for all the questions that you have. Show us what is that that don't work and pls don't do this after many hours-days. Then i'll not be able to support it.

Thanks for understanding.

[Ted Dennis]

Thanks for your understanding I will endeavour to try and sort this out in one go, unfortunately I will not be able to upload a sample until tomorrow. Then hopefully that will put the problem to bed.

Thanks again for you help

Cheers

[Fotis1991]

Good luck!

Edit: I saw your other thread here..

http://www.excelforum.com/excel-form...f-formula.html

[Ted Dennis]

This was another part of the jigsaw but not what you were looking at for me.
Cheers

[Ted Dennis]

[ATTACH]262223[/ATTACH)

I have attached the file hopefully this can sort out the issue

Thanks

Again

[Fotis1991]

Hi there.

...The other problem iis the Index function that you added fro me, this is not working, becasue where there is a repeat number in col D it keeps picking that up?

..I have attached the file hopefully this can sort out the issue

We can fix it using this ARRAY formula.

=INDEX($C$2:$C$14;MATCH(MAX(IF($A$2:$A$14=$G5;IF($B$2:$B$14=$G$1;$D$2:$D$14)))&$G5;$D$2:$D$14&$A$2:$A$14;0))

Comments?

[Ted Dennis]

This works very well, thanks again
Ted