Using DGET
 

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.

Using DGET
 

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

--
Jim
"GE Johnny" <GE wrote in message
...
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.

Using DGET
 

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" wrote:

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

--
Jim
"GE Johnny" <GE wrote in message
...
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.

Using DGET
 

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"

--
Jim
"GE Johnny" wrote in message
...
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" wrote:

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

--
Jim
"GE Johnny" <GE wrote in message
...
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.

Using DGET
 

GE Johnny <GE wrote...
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.

Using DGET
 

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" wrote:

GE Johnny <GE wrote...
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.

Using DGET
 

GE Johnny wrote...
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.

Using DGET
 

(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" wrote:

GE Johnny wrote...
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.

Using DGET
 

GE Johnny wrote...
(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.