Share via

DGET function - copy formula

Anonymous
2014-01-28T18:30:37+00:00

=DGET('COMPONENT REFERENCE'!$A$1:$C$77,'COMPONENT REFERENCE'!$C$1,D$1:D2) - this formula works great and i get the result expected however when I copied the formula above to the next row down it changes as follow:

=DGET('COMPONENT REFERENCE'!$A$1:$C$77,'COMPONENT REFERENCE'!$C$1,D$1:D3) - it now returns #NUM!

How can I get this to work properly? I am still reference the D1 cell but I want the criteria to change to cell D3 instead of the D2.

Thanks

Val

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2014-01-28T23:16:44+00:00

    Hi,

    Try this formula in cell B2 and copy down

    =VLOOKUP(A2,'Component reference'!$A$2:$C$77,3,0)

    Hope this helps.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-01-28T22:28:28+00:00

    This makes it difficult for two reasons.  First, you have multiple vendors listed for the same part.  Which one do you choose for a particular description?  Second, the description is only given for the first vendor.  For the second vendor, the description is blank.

    If you put the formula below in the "MFG/VENDOR PART NO" column on the other sheet (use SHIFT-CTRL-ENTER) and then drag down, it will find the first vendor that has a part with that description.

    Is that what you want?

    =IF(A2="","",INDEX('Component Reference'!$B$2:$B$77,MATCH(1,--('Component Reference'!$A$2:$A$77=Lookup!$A2),0),1)&" "&INDEX('Component Reference'!$C$2:$C$77,MATCH(1,--('Component Reference'!$A$2:$A$77=Lookup!$A2),0),1))

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-01-28T21:23:19+00:00

    Here is what I am trying to accomplish, let's say I have these data in (sheet2) named as "Component Reference

    DESCRIPTION MFG/VENDOR MFG/VENDOR PART NO
    35 POSITION AMPSEAL PLUG, BLACK TYCO 776164-1
    14 POSITION AMPSEAL PLUG, BLACK TYCO 776273-1
    8 POSITION AMPSEAL PLUG, BLACK TYCO 776286-1
    8 POSITION DT SERIES PLUG ASSEMBLY, GREY, FEMALE DEUTSCH INDUSTRIAL/TYCO DT06-08SA
    22 POSITION MINI-FIT JR. RECEPTACLE HOUSING, UL 94V-0 MOLEX 39-01-2225
    TYCO 2-1586019-2
    20 POSITION MINI-FIT JR. RECEPTACLE HOUSING, UL 94V-0 MOLEX 39-01-2205
    TYCO 2-1586019-0
    8 POSITION MINI-FIT JR. RECEPTACLE HOUSING, UL 94V-0 MOLEX 39-01-2085
    TYCO 1586019-8
    2 POSITION MINI-FIT JR. SERIES PLUG MOLEX 39-01-3029
    TYCO 1586017-2
    4 POSITION MICRO-FIT 3.0 RECEPTACLE HOUSING, UL 94V-0 MOLEX 43-025-0400
    DT SERIES WEDGE LOCK, ORANGE, 8 POSITION PLUG DEUTSCH INDUSTRIAL/TYCO W8S
    DEUTSCH CONTACT SEALING PLUG DEUTSCH INDUSTRIAL/TYCO 114017
    AMPSEAL FEMALE CONTACT, 20-16AWG, GOLD PLATED TYCO 770854-3
    DEUTSCH FEMALE CONTACT, SIZE 16, GOLD PLATED DEUTSCH INDUSTRIAL/TYCO 0462-201-1631
    MINI-FIT JR. MALE CONTACT, 24-18AWG, GOLD PLATED MOLEX 39-00-0219
    TYCO 1586314-4
    MINI-FIT JR. FEMALE CONTACT, 24-18AWG, GOLD PLATED MOLEX 39-00-0181
    TYCO 794956-4
    MICRO-FIT 3.0 FEMALE CONTACT, 20-24AWG, GOLD PLATED MOLEX 43-030-0009
    UNINSULATED FERRULE, 10MM, 22AWG PANDUIT F75-10-M
    T&B F9002
    UNINSULATED FERRULE, 8MM, 20AWG PANDUIT F76-8-M
    FERRULES DIRECT N07508
    UNINSULATED FERRULE, 10MM, 20AWG PANDUIT F76-10-M
    FERRULES DIRECT N07510
    UNINSULATED FERRULE, 12MM, 20AWG PANDUIT F76-12-M
    FERRULES DIRECT N07512
    UNINSULATED FERRULE, 10MM, 16AWG PANDUIT F78-10-M
    T&B F9008
    UNINSULATED FERRULE, 12MM, 16AWG PANDUIT F78-12-M
    T&B F9009
    INSULATED TWIN WIRE FERRULE, 12MM, 16AWG PANDUIT FTD78-12-D
    T&B F8007
    UNINSULATED FERRULE, 12MM, 10AWG PANDUIT F82-12-M
    T&B F9019
    UNINSULATED FERRULE, 12MM, 6AWG PANDUIT F84-12-TL
    T&B F9025
    UNINSULATED FERRULE, 15MM, 4AWG PANDUIT F85-25-C
    T&B F9032
    INSULATED FORK TERMINAL, LOCK TYPE, #6 STUD, 22-18AWG PANDUIT PV18-6LF-CY
    T&B 18RA-6FL
    INSULATED FORK TERMINAL, LOCK TYPE, #6 STUD, 18-14AWG PANDUIT PV14-6LF-C
    T&B 14RB-6FL
    INSULATED FORK TERMINAL, LOCK TYPE, #8 STUD, 12-10AWG PANDUIT PV10-8LF-L
    T&B 10RC-8FL
    INSULATED RING TERMINAL, #10 STUD, 22-18AWG PANDUIT PV18-10R-CY
    T&B 18RA-10
    INSULATED RING TERMINAL, 1/4’’ STUD, <br>22-18AWG PANDUIT PV18-14R-CY
    T&B 18RA-14
    INSULATED RING TERMINAL, 3/8’’ STUD, 22-18AWG PANDUIT PV18-38R-LY
    T&B 18RA-38
    INSULATED RING TERMINAL, 1/4’’ STUD, 16-14AWG PANDUIT PV14-14R-C
    T&B 14RB-14
    INSULATED RING TERMINAL, 1/2" STUD, 16-14AWG PANDUIT PV14-12R-L
    T&B RB14-12
    INSULATED RING TERMINAL, #10 STUD, 12-10AWG PANDUIT PV10-10R-L
    T&B 10RC-10
    INSULATED RING TERMINAL, 1/4’’ STUD, 12-10AWG PANDUIT PV10-14R-L
    T&B 10RC-14
    UNINSULATED RING TERMINAL, 1/4’’ STUD, 12-10AWG PANDUIT P10-14R-L
    T&B C10-14
    INSULATED RING TERMINAL, 3/8", 6AWG PANDUIT PV6-38R-X
    T&B REV737
    UNINSULATED RING TERMINAL, 1/4" STUD, 4AWG PANDUIT S4-14R-E
    T&B F10711
    METAL FILM AXIAL RESISTOR, 2 KOHMS, 0.25 W, 1% YAGEO MFR-25FRF-2K00
    WIREWOUND AXIAL RESISTOR, 120 OHM, 5W, 1% OHMITE 45F120E
    CORRUGATED LOOM TUBING, SLIT WALL, BLACK, 0.35" BUNDLE SIZE PANDUIT CLT35F-C20
    CORRUGATED LOOM TUBING, SLIT WALL, BLACK, 0.5" BUNDLE SIZE PANDUIT CLT50F-C20
    CORRUGATED LOOM TUBING, SLIT WALL, BLACK, 1.0" BUNDLE SIZE PANDUIT CLT100F-C20
    CORRUGATED LOOM TUBING, SLIT WALL, BLACK, 1.5" BUNDLE SIZE PANDUIT CLT150F-D20
    TWISTED TRIAD, SHIELDED, 3X18AWG, 20AWG DRAIN WIRE BELDEN 3089A
    STRIP .5" FROM END, LEAVE INSULATION

    Let's say I want to extract the MFG/VENDOR PART NO (Column C) from sheet2 and shown on sheet1 for a particular "DESCRIPTION" as the criteria.

    DESCRIPTION MFG/VENDOR PART NO
    INSULATED RING TERMINAL, 3/8’’ STUD, 22-18AWG
    UNINSULATED FERRULE, 12MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    INSULATED FORK TERMINAL, LOCK TYPE, #6 STUD, 22-18AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    UNINSULATED FERRULE, 12MM, 20AWG
    INSULATED RING TERMINAL, 1/2" STUD, 16-14AWG
    UNINSULATED FERRULE, 12MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    INSULATED RING TERMINAL, 1/2" STUD, 16-14AWG
    UNINSULATED FERRULE, 12MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    INSULATED RING TERMINAL, 1/2" STUD, 16-14AWG
    UNINSULATED FERRULE, 12MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG
    INSULATED RING TERMINAL, 1/2" STUD, 16-14AWG
    UNINSULATED FERRULE, 12MM, 20AWG
    UNINSULATED FERRULE, 10MM, 20AWG

    Was this answer helpful?

    0 comments
  4. Anonymous
    2014-01-28T19:11:52+00:00

    DGET may not be the best approach for you because of the limitation you have found in applying criteria.  Instead, you might try a combination of INDEX and MATCH functions.  For an example, let's say I have the data below on Sheet2:

    Date What Value
    1/7/2011 aaa 99
    2/14/2011 bbb 11
    5/24/2011 ccc 97
    7/23/2011 aaa 73
    8/29/2011 ccc 2
    10/9/2011 bbb 45
    11/25/2011 bbb 53
    1/10/2012 ccc 70
    3/19/2012 aaa 39
    4/24/2012 aaa 61
    7/11/2012 bbb 35
    9/12/2012 ccc 39
    12/11/2012 aaa 16
    1/27/2013 bbb 8

    Let's say I want to extract the Date (Column A) for a particular combination of "What" and "Value".  On Sheet1, we enter our criteria as below:

    What Value
    bbb 53
    ccc 70

    So first let's define a formula that will find the first combination, which is "bbb" and "53".  Enter the formula below in Cell C2, right next to "bbb" and "53":

    =INDEX(Sheet2!$A$2:$A$15,MATCH(1,(Sheet2!$B$2:$B$15=Sheet1!$A2)*(Sheet2!$C$2:$C$15=Sheet1!$B2),0),1)

    This is an array formula and should be entered using SHIFT-CTRL-ENTER.  You'll know you've done it right when the formula in the formula bar looks like this:

    {=INDEX(Sheet2!$A$2:$A$15,MATCH(1,(Sheet2!$B$2:$B$15=Sheet1!$A3)*(Sheet2!$C$2:$C$15=Sheet1!$B3),0),1)}

    Then drag the formula in C2 down to C3.  The result will look like this:

    What Value Date
    bbb 53 11/25/2011
    ccc 70 1/10/2012

    For this method, if you have more than one match, the first match in your database will be the one selected.  Hopefully you can adapt this approach to your data.

    HTH,

    Eric

    Was this answer helpful?

    0 comments