Trying to figure out IF formula

[Juan]

QHello all,

I have two sheets. Sheet1 contains about 4547 rows the sheet2 4645 rows.
Sheet 1 contains parts that are in sheet two so I'm trying to put a formula
that
if Sheet 1 colum E equals Sheet 2 column M, then put sheets 2 Column Q data
into Sheet 1 column K
I have put following formula in Sheet 1 column K
=IF(E:E='SHEET2'!M:M,'SHEET'!Q:Q,"no price")

This doesn't work right becuase sheet 1 has less records. Is it possible to
work around this?
Please advise.
Thanks for any help.
J

 

[acampbell012]

Juan,

Your formula won't work as written. I corrected for the error in
"'SHEET2'!Q:Q" error and the results return 0. I don't think it has
anything to do with the difference in range dimensions.

This returns the value in Q1 if the match argument is met for single
cells:

=IF(E1=Sheet2!M1,Sheet2!Q1,"no price")

Are you trying to determine if a value in sheet 1 is also in sheet 2 or
match on identical rows??

Alan

 

[Trevor Shuttleworth]

=IF(ISNA(VLOOKUP(E1,Sheet2!M:Q,5,FALSE)),"no
price",VLOOKUP(E1,Sheet2!M:Q,5,FALSE))

And drag down

Regards

Trevor

 

[kassie]

=IF(ISNA(VLOOKUP(E1,Sheet2!$M$1:$Q$4645,5,FALSE)),"",VLOOKUP(E1,Sheet2!$M$1:$Q$4645,5,FALSE)) and copy down

 

[Juan]

Hello Trevor/Alan/Kassie,
thanks alot for the help. I tested the revised formula and seems to be
working.

Really appreciated the quick replies.

All of you have a great weekend.

J