# Find max value from vlookup against multiple sheets

• Hi,

Hopefully this is an easy one that is just not coming to my mind.

I am trying to find the max value and sheet name by looking up a reference cell across multiple sheets which may/may not contain that reference value

I'll try to write it as an example to help you picture the issue I have.

Lets say I have sheet1 called "Animals"
Now lets say I have 10 sheets one for each Farm

Sheet1 will have all the Animals in Column A eg:

 Chicken Cow Horse Pig Sheep ...

Now each sheet for the Farm may or may not contain each of the animals eg:

Sheet2: Joes Farm contains the following:

 Chicken 500 Pig 60

Sheet3: Bobs Farm contains the following:

 Chicken 700 Pig 40 Sheep 20

Sheet4: Jacks Farm contains the following:

 Horse 100

So I'd like to enter a formula or VBA to have a result on sheet1 like this:

 Chicken Bobs Farm 700 Cow Horse Jacks Farm 100 Pig Joes Farm 60 Sheep Bobs Farm 20 ... ... ...

Michael

• Make a list of the sheetnames on the side somewhere and give the range a name like "TabNames".

then, assuming data in the Animals sheet starts in A2, then in B2 enter this Array* formula:

=IF(C2=0,"",INDEX(TabNames,MATCH(MAX(SUMIF(INDIRECT("'"&TabNames&"'!a:a"),A2,INDIRECT("'"&TabNames&"'!b:b"))),SUMIF(INDIRECT("'"&TabNames&"'!a:a"),A2,INDIRECT("'"&TabNames&"'!b:b")),0)))

copied down

and in C2 enter this Array* formula

=MAX(SUMIF(INDIRECT("'"&TabNames&"'!a:a"),A2,INDIRECT("'"&TabNames&"'!b:b")))

To hide 0 values format column C as Custom with Type: 0;-0;;@

[arf]*[/arf]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Thanks for those suggestions

I am getting results however they seem to be random.

It returns the correct farm that corresponds to the animal count, however if you have 3 farms with 30, 20, 50 it may return the farm with 30 or 20, its not always the first hit

What could I be doing wrong?

• It should work.

Are you confirming with CTRL+SHIFT+ENTER?

Are you sure their are no leading/trailing spaces in some of the entries?

You can attach a copy of a workbook showing incorrect results and I can review it for you.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Sorry for the very long delay.

So I did get it working by ensuring that the data imported is of type number. Thanks for your help on that.

All works will for MAX.

Now when I try to find the farm that contains the MIN for each animal, I just get 0

I simply changed

Quote

=MAX(SUMIF(INDIRECT("'"&TabNames&"'!a:a"),A2,INDIRECT("'"&TabNames&"'!b:b")))

to

Quote

=MIN(SUMIF(INDIRECT("'"&TabNames&"'!a:a"),A2,INDIRECT("'"&TabNames&"'!c:c")))

I am also confirming the array formula using CTRL+SHIFT+ENTER

Is there something else I need to change?

• It probably means that there is a blank cell next to a match in some sheet.

Try perhaps this formula:

=MIN(SUMIFS(INDIRECT("'"&TabNames&"'!c:c"),INDIRECT("'"&TabNames&"'!a:a"),A2,INDIRECT("'"&TabNames&"'!c:c"),">0"))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Hi,

I've tried:

=MIN(SUMIFS(INDIRECT("'"&TabNames&"'!c:c"),INDIRECT("'"&TabNames&"'!a:a"),A2,INDIRECT("'"&TabNames&"'!c:c"),">0"))

and I'm still getting zero. I've tried Enter, CSE, changing the final criteria but I get 0 all the time for any A\$ row, again it works if I change it to MAX so not sure when its not getting the MIN.

There are no hidden worksheets that contain the values in A with a blank and all the results in Column A have values in Columns B and C that are > 0

• Attach the workbook - someone will take a look for you.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Thanks Ali.

I have found that during evaluation, the SUMIFS contains many #Value! results for the various sheets that do not contain a result so the >0 criteria is (I'm guessing skipped), so the final MIN evaluation contains something like (0,0,0,50,0,0,28,0,0,0) so essentially I need to work out how to disregard the #Value! errors from evaluation.

Any help on that???

• Why won't you attach the workbook?

Have you tried IFERROR?

=IFERROR(your_formula,"")

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• You can use VBA in this case.
Place Animals sheet into sheet1 and other sheets into sheet2, sheet3,...
use function sheets for comparision through each sheet
example sheets(1).cells(1,1).value="chicken"
sheets(2).cells(1,2).value=500
use name property of sheets function to get the name of sheet2, sheet3,... note that you need to name the sheet2 like "Joes Farm" when you use function sheets(2).name will be "Joes Farm".
use for loop for get sheet from sheet2 to sheetn if you have many sheet, this make your file run automantically when you add new sheet of farm of someone.
I think if you know VBA you will make this app easy. HOpe you can do it.

• Ok. I know what is happening, the formula is looking in all sheets for each "animal". If the sheet does not have a match, the SUMIF returns 0. (I should've thought of that, but as Ali mentions a sample workbook is usually needed to catch those nuances).

Anyway, give this version a try:

=MIN(IF(SUMIF(INDIRECT("'"&TabNames&"'!a:a"),A1,INDIRECT("'"&TabNames&"'!b:b"))>0,SUMIF(INDIRECT("'"&TabNames&"'!a:a"),A1,INDIRECT("'"&TabNames&"'!b:b"))))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Hi All,

Apologies for not responding any sooner - again.

I got a chance to work on it again yesterday and worked out the MIN. I could have saved about 1 hour if I looked at the thread and saw [USER="14282"]NBVC[/USER] response as I worked it out with the same formula by adding the elusive if...>0.

So now I can list the min and max for each animal and the respective farms.

So to summarise:

For the farm with the least amount of a certain animal

Code
``=IF(C2=0,"",INDEX(TABNAMES,MATCH(MIN(IF(SUMIFS(INDIRECT("'"&TABNAMES&"'!c:c"),INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!c:c"),">0")>0,SUMIFS(INDIRECT("'"&TABNAMES&"'!c:c"),INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!c:c"),">0"))),SUMIF(INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!c:c")),0)))``

For the least amount of the animal

Code
``=MIN(IF(SUMIFS(INDIRECT("'"&TABNAMES&"'!c:c"),INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!c:c"),">0")>0,SUMIFS(INDIRECT("'"&TABNAMES&"'!c:c"),INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!c:c"),">0")))``

For the farm with the most amount of a certain animal

Code
``=IF(E2=0,"",INDEX(TABNAMES,MATCH(MAX(SUMIF(INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!b:b"))),SUMIF(INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!b:b")),0)))``

For the most amount of the animal

Code
``=MAX(SUMIF(INDIRECT("'"&TABNAMES&"'!a:a"),A2,INDIRECT("'"&TABNAMES&"'!b:b")))``

I hope this helps others.

[USER="14282"]NBVC[/USER] - How do I close this and mark your message as the solution

Thanks everyone else for your input.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!