VLOOKUP Issues

Hi,

In the attached workbook on the FRI sheet (F83) I have a VLOOKUP formula that is giving me an error. =VLOOKUP(MAX(C32:D39,C54:D61,C71:D78),(C32:D39,C54:D61,C71:D78),1,FALSE)

I want the formula to look through the C32:D78 ranges to find the greatest value and then display the corresponding value from column A, which is a time. I compiled the formula from something I saw on another site, but it is giving me the #N/A error. Eventually, this formula will make its way onto each page of the woorkbook.

Extra points for someone who can provide the formula to take all 7 of these eventual results (1 from each sheet) and add them to the actual SUMMARY worksheet in a yet to be determined field...especially if it can show the most-frequently occuring value if there is one.

Thanks in advance!!!

When I added your formula to the workbook, it gave me the number 12, which is the highest value, but what I really want is "1800-1900" which was the corresponding value in Column A.

=VLOOKUP(MAX($C$32:$C$78),($C$32:$C$78),1,FALSE)

This part:

Originally Posted by tapsmiled

I want the formula to look through the C32:D78 ranges to find the greatest value and then display the corresponding value from column A, which is a time.

is quite clear to me. You cannot use VLOOKUP to return results from the column, which is located left from the searched one. Instedad you cen use combination of 2 functions INDEX and MATCH in one formula:

Please Login or Register  to view this content.

As for the second part of your question:
Of course you can list "rush hours" for each day in the Summary sheet in a similar way you listed there for instance total calls. So once you copy the above formula into other days sheets, in a designated cell write:

Please Login or Register  to view this content.

and so on.

Alternatively, you could include sheetname in the formula and then it works no matter in which sheet is located. for example:

Please Login or Register  to view this content.

If you plan to find most frequent (in a given week) hours, and if there is a chance that some days (as it is now) have no data at all this formula shall be included inside IF function, like:

Please Login or Register  to view this content.

And last thing - showing the most frequent one (if there is one) can be done with conditional formatting of the cells with these formulas in summary sheet.
For instance the simple way - using a formula for formatting rule - in the attached file I selected in summary sheet range A25:G25
and used the folowing formula for conditional formatting rule

Please Login or Register  to view this content.

This is not exactly the most frequent, but one which occured twice or more. I do not know nature of the business, but it is probably worth noticing if for instance there were 2 most fequent hours.

Kopia DSR (BLANK BETA).xlsx

Hope it helps,

I actually like the idea of a chart, though I dont know how I would apply my results to it.

For clarification, this is for a group of 911 call centers. The number of calls can vary from as little as 2 to over a hundred, depending upon how busy each center is. For statistical purposes, we have to achieve standards above 90%, and this is based upon the busiest hour of the day. Each week, the supervisors for each center need to report their numbers, and I want to simplify the process.

Thank you Haseeb. It actually did work. When there are no numbers entered, it reads TOTAL, and when there is data, it is correctly selecting the appropriate value from column A.

Wow, am I really this dumb? I hid all of the rows at the bottom of each page. How do I show them again so that i can try to enter the chart? I know....noob question

Tried that; it didnt work.

Tried that too. I don't get it.

I tried that as well. If you look at the workbook in the original post, you will see that B28 is the last visible row on SUMMARY. Nothing I have tried has revealed B29+

Originally Posted by FDibbins

If you click on the row number (28) and "drag down" as if there was another row beneath, then right-click on 28, select unhide

That did it, though it completely locked up my computer for about 10 minutes. Okay...back to that chart now.

I did eventually. Regarding that chart now...I did as you instructed, and it returned the earliest times with the MAX value. Is there a way, using your formula, to post ALL of the times =MAX. In other words, if 3 (or more) separate times have the same total, could I list each of those times on the chart?

Here is the latest version of the workbook. As you can see on SAT, there are 3 separate instances where the call volume =10 (the MAX), but at the bottom (F83) and on the SUMMARY, it only lists a single instance rather than each instance. I know the calculation in F83 is the one that needs to be altered; I just dont know how.

Actually, the file is too large now to upload

That would do it, but it seems like I would have to make massive alterations to what I just finished writing. You are awesome though. I guess I will have to leave it as it is.....can't hand them EVERYTHING on a silver platter. LOL

Thank you to everyone who contributed!!! You guys are great!!!

Thank you!