Using DGET

[GE Johnny]

I am using DGET to retrieve inforamtion on a spreadsheet. My problem is Excel
is seeing duplicate information when there is none. Excel is seeing MGC-HDVM
and MGC-HDVMH as the same text. If I remove the dash then the problem goes
away. These are part numbers I am looking up and I would like to show them as
they appear with the dashes. I have the same problem if I use the find
function. When I type in MGC-HDVM and click on find it also displays
MGC-HDVMH as if it were the same text.

 

[Jim Rech]

Re Find, make sure you select "Match entire cell contents".

 

[GE Johnny]

I see were you can use that selection if you are using the find command in
edit. How do I apply that function to my formula:
=IF(E6=1,(DGET(Sheet2!A1:F191,"ALM_Current
24HRMS",H5:I6)),(DGET(Sheet2!A1:F191,"ALM_Current 24LRMS",H5:I6)))

 

[Jim Rech]

I haven't used DGET beyond playing with it a little. I tried to reproduce
your example but it seemed to work. You did enter your critera cell like
this?: ="=MGC-HDVM"

 

[Harlan Grove]

I am using DGET to retrieve inforamtion on a spreadsheet. My problem
is Excel is seeing duplicate information when there is none. Excel
is seeing MGC-HDVM and MGC-HDVMH as the same text. If I remove the
dash then the problem goes away. These are part numbers I am looking
up and I would like to show them as they appear with the dashes. I
have the same problem if I use the find function. When I type in
MGC-HDVM and click on find it also displays MGC-HDVMH as if it were
the same text.

This is one of many problems with Excel's DGET function.

As a practical matter, you'd be better off using an array formula
which calls MATCH. Given the formula example in your follow-up
message, try the array formula

=INDEX(Sheet2!A1:F191,MATCH(TRUE,(H6=<this>)*(I6=<that>),0),
MATCH(IF(E6=1,"ALM_Current 24HRMS","ALM_Current 24LRMS"),
Sheet2!A1:F1,0))

where H6=<this> and I6=<that> are substitutes for the criteria in
H5:H6 and I5:I6, respectively.

 

[GE Johnny]

Thanks so much for the help. I have been trying to get this array to work but
I've had no success. I keep getting #N/A for a result. I think it might be
something with my criteria for cells H6 and I6.

 

[Harlan Grove]

Thanks so much for the help. I have been trying to get this array
to work but I've had no success. I keep getting #N/A for a result.
I think it might be something with my criteria for cells H6 and I6.

....

Almost certainly, so show us the cell contents for your DGET criteria
range.

 

[GE Johnny]

(Cell H5) NAC_DevDes (Cell I5) ProductID
(Cell H6) 30cd (Cell I6) MG1-HDVM

My Old Formula in (Cell H8)
=IF(E6=1,(DGET(Sheet2!A1:H191,"ALM_Current
24HFRW",J5:K6)),(DGET(Sheet2!A1:H191,"ALM_Current 24LFRW",J5:K6)))

Your Formula
=INDEX(Sheet2!A1:F191,MATCH(TRUE,(H6=<this>)*(I6=<that>),0),
MATCH(IF(E6=1,"ALM_Current 24HRMS","ALM_Current 24LRMS"),
Sheet2!A1:F1,0))

 

[Harlan Grove]

(Cell H5) NAC_DevDes (Cell I5) ProductID
(Cell H6) 30cd (Cell I6) MG1-HDVM

My Old Formula in (Cell H8)
=IF(E6=1,(DGET(Sheet2!A1:H191,"ALM_Current 24HFRW",
J5:K6)),(DGET(Sheet2!A1:H191,"ALM_Current 24LFRW",J5:K6)))

Note that your ORIGINAL formula was

=IF(E6=1,(DGET(Sheet2!A1:F191,"ALM_Current 24HRMS",
H5:I6)),(DGET(Sheet2!A1:F191,"ALM_Current 24LRMS",H5:I6)))

so you've changed "ALM_Current 24HRMS" to "ALM_Current 24HFRW",
"ALM_Current 24LRMS" to "ALM_Current 24LFRW", and F191 to H191.
Presumably you made these changes in the formula I provided as there
would have been no way for me to have anticipated this without having
read your mind.

Your Formula
=INDEX(Sheet2!A1:F191,MATCH(TRUE,(H6=<this>)*(I6=<that>),0),
MATCH(IF(E6=1,"ALM_Current 24HRMS","ALM_Current 24LRMS"),
Sheet2!A1:F1,0))

....

OK, if the NAC_DevDes field were in column B and the ProductID field
in column C, try the array formula

=INDEX(Sheet2!A1:F191,
MATCH(TRUE,(H6=Sheet2!B1:B191)*(I6=Sheet2!C1:C191),0),
MATCH("ALM_Current 24"&IF(E6=1,"HRMS","LRMS"),Sheet2!A1:F1,0))

That is, type the formula and hold down [Ctrl] and [Shift] keys before
pressing the [Enter] key to enter this as an array formula.