Replacing VLOOKUP with DGET

[AussieExcelUser]

I am wanting to replace some VLookup formulas with the DGet function but cannot get the DGet to work. The syntax seems simple enough but all I get is #N/A errors. The criteria exist in the database range, are not duplicated and I am trying to retrieve the value from the second column of the database range as such:

=DGET(E6:F500,2,I2:I500)

A possible complexity is that the database range is actually part of a pivot table and that I cannot use headings for the columns, or is it simply that you cannot use DGET when your criteria is a long list rather than just a heading and one criteria value?

Any suggestions would be appreciated.

[Bryan Hessey]

I am wanting to replace some VLookup formulas with the DGet function but cannot get the DGet to work. The syntax seems simple enough but all I get is #N/A errors. The criteria exist in the database range, are not duplicated and I am trying to retrieve the value from the second column of the database range as such:

=DGET(E6:F500,2,I2:I500)

A possible complexity is that the database range is actually part of a pivot table and that I cannot use headings for the columns, or is it simply that you cannot use DGET when your criteria is a long list rather than just a heading and one criteria value?

Any suggestions would be appreciated.

No idea about the Pivot table, but I think the headers on the two ranges must match for the DGET, at least that is apparent in getting the 'help's =DGET(A4:E10,"Yield",A1:A3) to work

I was not able to produce the #N/A though, just the #NUM and #Value as expected.

Not sure about your 'I2:I500' - it seems large for a criteria range, applying 499 conditions to 495 rows of data looks odd, am I missing something?

---

[AussieExcelUser]

G'day Bryan,

I'll try playing around with headers.

Actually, the complete Lookup (as it stands at the moment) is comparing over 3000 to 495!

I might have a go at converting the Lookups to an array and see if I can gain any sort of speed improvement that way (which is what I am after).

That gives me an additional issue in that I cannot get an array to copy down and increase the criteria reference cell each row.

[Bryan Hessey]

G'day Bryan,

I'll try playing around with headers.

Actually, the complete Lookup (as it stands at the moment) is comparing over 3000 to 495!

I might have a go at converting the Lookups to an array and see if I can gain any sort of speed improvement that way (which is what I am after).

That gives me an additional issue in that I cannot get an array to copy down and increase the criteria reference cell each row.

If you were converting a Lookup to an Array, would you not lean towards
Index(Match,Match) rather than DGet, or do you need the additional criteria that Lookup-True or Match-MatchType cannot give?

---

[AussieExcelUser]

I'll try out your indexing suggestion and see how that goes.

What the lookup does is simply look for an exact match of the value in column I with a value in column E and return the adjacent value in column F.

Simple stuff but as there are so many lookup calculations the entire workbook is very slow to recalculate (which is set to manual).

Cheers,

Grant.

[Bryan Hessey]

I'll try out your indexing suggestion and see how that goes.

What the lookup does is simply look for an exact match of the value in column I with a value in column E and return the adjacent value in column F.

for a Lookup,False, yes. The 'True' version is for sorted numerics.

Simple stuff but as there are so many lookup calculations the entire workbook is very slow to recalculate (which is set to manual).

Cheers,

Grant.

Lookups are noted for slowing down the response each time the worksheet calculates. Unfortunately I have nothing that suggests that a Match will be any significant amount quicker, unless the Index replaces a HLookup/VLookup pair.

Let me know how you go

---