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

Updated 14th October 2017