extents of Vlookup 2

[eit09]

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?

 

[magoo2]

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

 

[eit09]

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.

 

[IDS]

You need DMAX()

I have updated your spreadshhet with an example.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[eit09]

IDS,

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.

 

[IDS]

eit09,

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

http://newtonexcelbach.wordpress.com/

 

[eit09]

IDS,

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

 

[IDS]

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

http://newtonexcelbach.wordpress.com/

 

[IDS]

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:
="=entry"

2) Use an array formula:
=MAX(IF(EXACT($C$7:$C$12928,U7),$E$7:$E$12928,-1000000))
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

http://newtonexcelbach.wordpress.com/