DGET function

Wouldn't it work if you changed the criteria range to A1:F3?

BarrieBarrie Davidson

That was just an example my criteria range will change depending on the row I am on ie it could be a1:f1 and a700:f700

Basically what I am trying to do is populate a column in a second list (column G) with a field from another list as I go down this second list the criteria is in cells a:f of the same row

Any further suggestions would be appreciated

Cells in a Criteria range must be contiguous (nt)

See Mark's response (nt)

Non contiguous range

I found this in the depths of the Microsoft Knowledge base. I can get it to work down a column but not accross a row. According to Microsoft you can embedd the makearray () formula in another formula to specify a non contiguous range of cells as contiguous

The document :-

This function takes any contiguous range of cells as its arguments. Nonadjacent ranges are separated by commas.
Sample Visual Basic Procedure
Function MakeArray(ParamArray CellAddress()) As Variant

' Declaration of function variables.
Dim Temp As Variant
Dim TheArray() As Variant
Dim Count As Integer, Ver as Integer
Dim W As Integer, X As Integer, Y As Integer, Z As Integer

' Initialize the Count variable.
Count = 1
' Set the variable Ver = 0 if the version of Microsoft Excel is
' greater than 8 (8 is Microsoft Excel 97 for Windows).
If Left(Application.Version, Len(Application.Version) - 1) >= 8 Then
Ver = 0
Else
Ver = 1
End If
' Set variable X from Ver to the total number of arguments in
' the CellAddress array.
For X = Ver To UBound(CellAddress, 1)
' Temp equals the first element of the CellAddress array.
Set Temp = CellAddress(X)

' Test Temp to see whether it is an array.
If IsArray(Temp) Then

' If Temp is an array, set Y from 1 to the total number
' arguments in the Temp array's first dimension.
For Y = 1 To UBound(Temp.Value, 1)

' If Temp is an array, set Z from 1 to the total number
' arguments in the Temp array's second dimension.
For Z = 1 To UBound(Temp.Value, 2)

' ReDimension TheArray, Preserving any existing
' values, from 1 to Count.
ReDim Preserve TheArray(1 To Count)

' TheArray, element Count equals Temp, element Y in the
' first dimension by element Z in the second dimension.
TheArray(Count) = Temp(Y, Z).Value

' Increment the Count variable by one.
Count = Count + 1
Next Z

Next Y
' If Temp is not an array, proceed from here.
Else
' ReDimension TheArray, preserving any existing
' values, from 1 to Count.
ReDim Preserve TheArray(1 To Count)

' TheArray element Count equals Temp.
TheArray(Count) = Temp

' Increment the Count variable by one.
Count = Count + 1

' End the block If statement.
End If
Next X
' Return TheArray to our function MakeArray.
MakeArray = TheArray

End Function
To Use This Example
Enter the following information in a worksheet:
A1: 1 B1: 2 C1: <empty> D1: 5
A2: 3 B2: 4 C2: <empty> D2: 6

On the worksheet, select cells A4:F4, and type the following formula:
=MakeArray(A1:B2,D1:D2)
NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

The resulting formula will resemble the following example:
A4:1 B4:2 C4:3 D4:4 E4:5 F4:6