Find max value from vlookup against multiple sheets

CompleXcel · Apr 26th 2018

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
...	...	...

Thanks for your help in advance!

Michael

**NBVC** · Apr 26th 2018

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]

CompleXcel · Apr 27th 2018

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?

**NBVC** · Apr 27th 2018

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.

CompleXcel · Jul 5th 2018

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?

**NBVC** · Jul 5th 2018

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"))

CompleXcel · Jul 6th 2018

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

**AliGW** · Jul 6th 2018

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

CompleXcel · Jul 6th 2018

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???

**AliGW** · Jul 6th 2018

Why won't you attach the workbook?

Have you tried IFERROR?

=IFERROR(your_formula,"")

01652845663 · Jul 6th 2018

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.

**NBVC** · Jul 6th 2018

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"))))

CompleXcel · Aug 14th 2018

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.

Find max value from vlookup against multiple sheets

Participate now!

Share