Lookup Q

[John]

I have a list of Raw Material items in Column A with their costs associated
with them in Column B. I want to design a recipe module whereby I select Raw
Materials that go in to Finished products and their associated costs. Is it
possible to do a LOOKUP if the LOOKUP range in not sorted alphabetically?

Thanks

 

[Manish]

Yes.

 

[John]

Thnaks Manish, the answer I thought I'd hear. Is there any other function I
can use to 'lookup' a value i..e select Raw Material name and return in
another column its cost?

 

[RagDyer]

To be *Exact*,
Vlookup, Hlookup, and Index-Match combinations can return exact data without
the list being sorted.

On the other hand the *LOOKUP* function *would* need a sorted list to return
accurate data.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Yes.

 

[John]

I'm using the formula below to find Raw Materials that are selected in
Column D, where the RM costs are in a Range A196:H266 in sheet "Master". The
costs are in Column H; RM are in Column A it returns a #N/A - what am I
doing wrong?



=IF(D9=0,"
",(INDEX(Master!A$196:H$266,MATCH(D9,Products),MATCH("Cost.",Master!$A$194:$
H$194,))))

 

[RagDyer]

Your description is confusing, with costs in A196:H266 AND costs in column
H?!?
Plus, what are the ranges that your range names represent?

Also, you're using "Cost.", *with* parenthesis and a decimal?!?

Could you post back with a more accurate description of your data list, and
the ranges represented by the range names you're using.


--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


I'm using the formula below to find Raw Materials that are selected in
Column D, where the RM costs are in a Range A196:H266 in sheet "Master". The
costs are in Column H; RM are in Column A it returns a #N/A - what am I
doing wrong?



=IF(D9=0,"
",(INDEX(Master!A$196:H$266,MATCH(D9,Products),MATCH("Cost.",Master!$A$194:$
H$194,))))

 

[John]

It was my attempt at a formula RD thats why its probably confusing!

Within the Range A196:H266 the RM name is located in Column A; costs of
these RM's is located in Column H

D9 is the cell I select a Raw Material, thus the cost I want represented is
the cost of that

'Food' is a range name I've set up but think this incorrect in the formula
as it is just a list of Finished Products which is irrevelant in obtaining
the cost of each Raw Material.

I spotted the DOT in 'Cost' - Cost is a Heading in Column H

Thanks

 

[RagDyer]

Try either of these.
Enter your sheet names as necessary.

=VLOOKUP(D9,A196:H266,8,0)

=INDEX(H196:H266,MATCH(D9,A196:A266,0))

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

It was my attempt at a formula RD thats why its probably confusing!

Within the Range A196:H266 the RM name is located in Column A; costs of
these RM's is located in Column H

D9 is the cell I select a Raw Material, thus the cost I want represented is
the cost of that

'Food' is a range name I've set up but think this incorrect in the formula
as it is just a list of Finished Products which is irrevelant in obtaining
the cost of each Raw Material.

I spotted the DOT in 'Cost' - Cost is a Heading in Column H

Thanks

 

[John]

Nah both are returning #N/A

Thanks for looking at it RD

 

[Harlan Grove]

Nah both are returning #N/A

...

Then you don't have an *exact* match for D9 in A196:A266.
...

 

[RagDyer]

Take the time to manually enter a RM in the list, and then *copy* the entry
into D9!... And see what happens!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Nah both are returning #N/A

Thanks for looking at it RD

 

[John]

That maybe what it appears Harlan, except that I use Data Validation - List,
which is a range from A196:A266 to select my Raw Material

 

[John]

Nothing, still #N/A

I'm logging off now, up since 5:30am

Thanks all

 

[Harlan Grove]

That maybe what it appears Harlan, except that I use Data Validation - List,
which is a range from A196:A266 to select my Raw Material

If D9 contains some value from A196:A266, but the formula

=VLOOKUP(D9,A196:H266,8,0)

returns #N/A, then either your Excel installation has become corrupted or D9
really doesn't exactly match anything in A196:A266 regardless of how you're
setting D9. Note that you can paste 'invalid' entries into cells with data
validation setting without triggering data validation errors or warnings.

If the formula above is returning #N/A, what does the following formula return?

=SUMIF(A196:A266,D9)

That said, your earlier posts showed references to a different worksheet. Are
you using the same range in the same worksheet as both the data validation list
and the first/leftmost column of your lookup table? If not, are you absolutely
sure the data validation list and the first/leftmost column of your lookup table
are identical?

 

[RagDyer]

If you truly *copied* and *pasted* a value from A196:A266 into D9, that
means you *no longer* have a data validation drop down list in D9!
The paste would have replaced it with the exact value that you copied.
Is this TRUE?
Did you lose the validation drop down list?

If you *did* lose the drop down, meaning you have an exact copy in both the
list and cell D9, the only thing I can think of that would still return a
#N/A error is that you have a mistake in your locations.
If your sheet names are incorrect, in spelling and/or actual existance, you
could have that error returned.

If you didn't lose the drop down list, then I'm not convinced that you
copied and pasted the exact value from A196:A266 into cell D9.

There is a way to accomplish this copy without losing the list, but I doubt
if you did it with that particular procedure (Paste Special - Values).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Nothing, still #N/A

I'm logging off now, up since 5:30am

Thanks all

 

[Harlan Grove]

...
...

There is a way to accomplish this copy without losing the list, but I doubt
if you did it with that particular procedure (Paste Special - Values).

...

It's also possible to select text in other applications (or even in Excel's own
formula bar), copy, then just paste into the 'validated' cell. That'll change
the contents of the cell without changing its formatting or validation settings.

 

[RagDyer]

Chalk that up to,
"Learn something new every day." (formula bar)

However Harlan, is Word included in "other applications"?
'Cause a copy and paste from Word wiped out the drop down list!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

...
...

There is a way to accomplish this copy without losing the list, but I doubt
if you did it with that particular procedure (Paste Special - Values).

...

It's also possible to select text in other applications (or even in Excel's
own
formula bar), copy, then just paste into the 'validated' cell. That'll
change
the contents of the cell without changing its formatting or validation
settings.

 

[Harlan Grove]

However Harlan, is Word included in "other applications"?
'Cause a copy and paste from Word wiped out the drop down list!

....

Plain text pastes without affecting formatting. Rich text pastes and changes
formatting.

 

[RagDyer]

By Rich Text, do you mean .rtf files?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

However Harlan, is Word included in "other applications"?
'Cause a copy and paste from Word wiped out the drop down list!

....

Plain text pastes without affecting formatting. Rich text pastes and changes
formatting.

 

[Harlan Grove]

By Rich Text, do you mean .rtf files?

...

They're related. Rich text is what's stored in RTF files, but it's also a format
the Windows Clipboard supports, like HTML.