Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

extents of Vlookup

extents of Vlookup

extents of Vlookup

I am trying to use the vlookup function for a particular situation. I have attached my excell file for convienence. What I am trying to accomplish is on the max load worksheet write a formula to search the table in the DATA worksheet. I'ts pretty simple there are multiple values in the data worksheet for each member. in the max load worksheet I want the max load for each member to show. Can any one give me an example formula that would work?

RE: extents of Vlookup

It's a lot simpler if you just use a pivot table.  It normally looks for the sum of a variable, but you can select maximum if that's what you need.

Result will look like:

Row Labels    Max of  x - Axial (kips)
M1    122.122
M2    206.935
M3    113.709
M4    8.511
Grand Total    206.935


RE: extents of Vlookup

The pivot table won't work for my situation.I just need a formula that I could copy in each cell. I just pasted a few members in this attached file for simplicity the file i am working with has much more data and I do not need the grand total. THanks for the post as I could see how handy a pivot table is.


RE: extents of Vlookup


Could you take a look at the attached excel sheet and show how to use dmax (same concept of first attached file just full version of my spreadsheet)? I can't seem to make the dmax operation work for my file.

Or if someone has another idea I'm all ears.


RE: extents of Vlookup


Unfortunately in Excel the DMax function criteria has to be a range with at least two rows, the column name, then the criterion, in your case the member label.  Lotus 123 has a much more flexible Dmax function, but I don't suppose that's any use to you.

I have fixed up your spreadsheet so that it works, but because you have over 500 categories I saved it as an XL2007 file, so I could keep all the criteria ranges on one row.  If you don't have XL 2007 let me know and I'll arrange it to work on 2003 or earlier.

What I did was:

1.  Extract all the unique member labels
2.  Copy them into a row usinge paste-special-transpose
3.  Copy the column name (Member Label) into the row above
4:  Enter a formula to generate addresses of the criteria ranges: =ADDRESS(7,X7)&":"&ADDRESS(8,X7), where x7 contains the column number (25 upwards)
5:  Enter the DMax formula as:=DMAX($C$6:$E$12928,3,INDIRECT(W7)), where w7 contains the address of the criteria range.
6.  Copy down for all the different mamber labels
7.  On the front sheet use the lookup function to look up the values from the dmax table.

I hope that makes sense.

No doubt many people would say that you would be better off with a proper database program for the amount of data that you are working with, but it's taken me longer to write this reply than do the actual work.


Doug Jenkins
Interactive Design Services

RE: extents of Vlookup


Thanks you definitely know what you're doing in excel! Unfortunately that's over my head and I will need to study your formulas for a while to get a grasp of the functions you used. Thanks for taking the time to explain and edit my excel sheet. Once I figure out all the functions you used do you think the best way to create a template would be to use the macro record feature excel offers (i.e. for the copy and transpose pasteing)?

RE: extents of Vlookup

eit09 - Please read below if you are using the spreadsheet I posted.

The DMAX function seems to give incorrect results, for instance if the criterion is M2 it returns 318.458, which is the maximum for M23.  It seems to match any string starting with M2.

If anyone has had similar problems with DMAX (or other D functions) I'd be interested to hear them.


Doug Jenkins
Interactive Design Services

RE: extents of Vlookup

A couple of solutions to the problem above:

1) Enter the criteria as: ="=M1"
This will display as =M1

Excel help says:
Because the equal sign is used to indicate a formula when you type text or a value in a cell, Microsoft Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

2)  Use an array formula:
where U7 contains the categories (entered as M1 etc)
To enter this formula as an array formula press ctrl-shift-enter, rather than just enter.

I have uploaded an example (2007 format)

Doug Jenkins
Interactive Design Services

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close