Cell entries dependent on other cells

**pie-eater** · 07-19-2010, 02:44 PM

Hello,

not be on for a looong time, but am stuck and thought that someone with far superior knowledge would be able to help!

Apologies for the ambiguous title, by the way!!!!!

Thanks to all for their previous assistance.

As an example, I have two columns in a simple spreadsheet..

From what I input into A2 & B2, I want the result to show in C2. However, this should only be if they are equal.

As an example:

If A2 & B2 equal "A", then C2 will equal 1

If A2 & B2 equal "B", then C2 will equal 2

If A2 & B2 equal "C", then C2 will equal 3

However, if A2 and B2 don't match, then C2 will stay blank.

Thanks for having a look and thanks in advance for your assistance.

Regards,

Craig

**NBVC** · 07-19-2010, 02:50 PM

Try:

=IF(A2=B2,LOOKUP(A2,{"A","B","C"},{1,2,3}),"")

note: The first array in { } must be in ascending sorted order.

**wmfinance** · 07-19-2010, 02:52 PM

How big of a spreadsheet are you working with?

**pie-eater** · 07-19-2010, 02:55 PM

Originally Posted by NBVC

Try:

=IF(A2=B2,LOOKUP(A2,{"A","B","C"},{1,2,3}),"")

note: The first array in { } must be in ascending sorted order.

Thankyou for the above, it does work.

However, when I copy the formula down, I get '#N/A'

How could I get rid of that please?

Sorry to be a pain!!!

**pie-eater** · 07-19-2010, 02:56 PM

Originally Posted by wmfinance

How big of a spreadsheet are you working with?

It will be about 400 rows long maximum

**NBVC** · 07-19-2010, 03:10 PM

Originally Posted by pie-eater

Thankyou for the above, it does work.

However, when I copy the formula down, I get '#N/A'

How could I get rid of that please?

Sorry to be a pain!!!

That is when both cells are blank?

=IF(AND(A2="",B2=""),"",IF(A2=B2,LOOKUP(A2,{"A","B","C"},{1,2,3}),""))

or

=IF(A2=B2,LOOKUP(A2,{0,"A","B","C"},{"",1,2,3}),"")

**NBVC** · 07-19-2010, 05:02 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

http://www.mrexcel.com/forum/showthread.php?t=482214