IF versus LOOKUP versus ARRAY?

Hi,

One way would be to create a table with the colours along the same row and the objects underneath each colour in order. e.g.
Red Blue
Toy Fish
Car Bike
Lamp Jar
..etc

Name this list say 'Lists'

Now in A2 copied down enter

Formula:

Please Login or Register  to view this content.

change the '4' in the formula to reflect the number of colour lists you have

Originally Posted by Gtrtim112

Thanks, Richard! I'll give it a whirl and let you know how it comes out.

I do have another scenario within this same spreadsheet that may fall under this same line of thinking but may need to be figured a little differently.

Once A2, A3 & A4 have been determined (I'm pretty certain that your trick above will work), I then need to have D2 (and D3, D4, etc) = a number. This number will either be equal to B2 or it will be B2 divided by C2. The word that populates in A2 will determine this.

-----A-----B-----C------D
1. RED ---5-----1------5
2. TOY ---16-----8-----2

The tricky part for me on this is that sometimes it will need to equal another cell and sometimes it needs to divide the two numbers. It all depends on what is in A of the same row.

If you have any ideas there, let me know. I'll work the list part out first and then attach an example if you think it would help.

Thank you!

It is hard to go without a sample Worksheet, but try:
D2=IF(OR(A1={"RED","BLUE","YELLOW"}),B1,B1/2)
Drag down.
If it does not work, upload a sample WS.