Lookup formula question
 

Hi all,

I have a Lookup formula setup to validate what the user enters against
a list of stock codes, returning the item costs. Stock codes are
A001, A002 etc, B001, B002, etc, D001, D002 etc If I then enter a
stockcode starting with letter C, I get the item cost returned from the
last letter B stockcode. How can I get it to return P.O.A.

This is what I have at present.

=IF(C4="Fittings",(LOOKUP(F4,Fittings!$C$1:$C$5019 ,Fittings!$D$1:$D$5019)),0)*AL4

C4 - Parent stock item description
F4 - Stock code
AL4 - Quantity

Any help - greatly appreciated.


--
PeterG
------------------------------------------------------------------------
PeterG's Profile: http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=386727

Try something like:

=IF(ISNA(MATCH(F4,Fittings!$C$1:$C$5019,0)),"POA", IF(C4="Fittings",(VLOOKUP(
F4,Fittings!$C$1:$D$5019,2,FALSE),0)*AL4)


Mangesh



"PeterG" wrote in
message ...

Hi all,

I have a Lookup formula setup to validate what the user enters against
a list of stock codes, returning the item costs. Stock codes are
A001, A002 etc, B001, B002, etc, D001, D002 etc If I then enter a
stockcode starting with letter C, I get the item cost returned from the
last letter B stockcode. How can I get it to return P.O.A.

This is what I have at present.


=IF(C4="Fittings",(LOOKUP(F4,Fittings!$C$1:$C$5019 ,Fittings!$D$1:$D$5019)),0
)*AL4

C4 - Parent stock item description
F4 - Stock code
AL4 - Quantity

Any help - greatly appreciated.


--
PeterG
------------------------------------------------------------------------
PeterG's Profile:
http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=386727

That looks good, how would i set it up if I have 6 stock code listings
on 6 seperate worksheets.

When I try this formula ....

=IF((OR(ISNA(MATCH(F4,Duct!$C$1:$C$5000,0)),ISNA(M ATCH(F4,Extra!$C$1:$C$5000,0)),ISNA(MATCH(F4,Fitti ngs!$C$1:$C$5000,0)),ISNA(MATCH(F4,Flex!$C$1:$C$50 00,0)),ISNA(MATCH(F4,Other!$C$1:$C$5000,0)),ISNA(M ATCH(F4,Silencer!$C$1:$C$5000,0)))),"POA","
")

It always returns the POA answer, even if one of the ISNA(Match
statements returns a false state.


--
PeterG
------------------------------------------------------------------------
PeterG's Profile: http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=386727

I use an AND instead of an OR .... thats fixed it.


--
PeterG
------------------------------------------------------------------------
PeterG's Profile: http://www.excelforum.com/member.php...o&userid=16485
View this thread: http://www.excelforum.com/showthread...hreadid=386727