DGET

This is just a tip but I might well turn it into a full page … let me know what you think. I have added to this page after I read two pages on INDEX-MATCH, see below for the links.

I am firmly of the opinion that not enough people consider DATABASE functions when looking for things, when finding averages, minima, maxima and so on, from a database. So take a look at the extra information starting with the heading DGET UPdate and there is a file to download now, again see below.

Like many people I have spent a lot of time using and worrying about the VLOOKUP function and whether it can read right to left as well as left to right.

Well, I have been thinking about this and have been playing with the DGET function and it can do in a very simple way.

I have used a couple of simple examples for you to consider and the screenshot below shows them. Take a look and see what you think. If you have been sweating over right to left VLOOKUP maybe those sweating days are over!

(click on the image to see the full size version)

There is no file to download as I have given you everything you need in that screenshot but if you want a larger set of examples to consider let me know and I will look at DGET, DSUM, DMIN and DMAX too.

DGET UPdate

I wrote the following comment at the end of this page: ExcelChamps

Good work. Nice and clear and loads of examples, which always help. Can I suggest, however, that you consider using or demonstrating two further approaches

OFFSET
DATABASE functions … in this case DGET

The OFFSET function is not perfect because it might need a helper cell or column to make it work but then again, so does INDEX-MATCH

The real winner for me are the DATABASE functions. Of course, these functions need a criteria range in order to work but that’s still essentially the same as INDEX-MATCH and OFFSET. Here is your INDEX-MATCH formula for finding the score given the roll number … =INDEX(D5:D14,MATCH(G7,A5:A14,0)) (my file has a sightly different layout to yours) and here is my DGET formula: =DGET($A$4:$D$14,D$4,H11:K12). OK, very similar in size.

On the other hand, here is my DAVERAGE formula for finding the average score of roll numbers greater than 5 who are in Section A =DAVERAGE($A$4:$D$14,D4,H20:K21): almost no changes needed from the DGET function. I think you would have to abandon INDEX-MATCH to find the average and use this if you didn’t use DAVERAGE, =AVERAGEIFS(D5:D14,A5:A14,H21,C5:C14,J21)

Please note, I am not being critical of what you did; but I am a great fan of the greatly unknown and unused DATABASE functions!

The other page I read is vlookup v index-match

Here is the file I created as I discussed these options: database_functions_again

Duncan Williamson

Updated 14th October 2017