[Solved] Help with IF THEN OR Statement in Calc

[bradyso]

I need a functional formula for OpenOffice Calc that will function like this...

if N3 = "Optics" then "Tactical" or if N3 = "Flashlight" then "Matches"

Basically I need multiple if then statements in one cell so that it can return multiple potential results based on whatever word is placed in there. I spent 2 hours looking but found nothing.

Please please help!

[FJCC]

I wouldn't us an If Then formula. Try a VLOOKUP(). For the example you give you would need to use two columns in any convenient spot on your sheet. Let's say columns Y and Z. In Y1 write Optics, in Z1 Tactical, in Y2 Flashlight and in Z2 Matches. You formula would then be

Code: Select all

=VLOOKUP(N3;Y1:Z2;2;0)

It will look in cells Y1 and Y2 for an exact match to the contents of N3 and return the corresponding value in column Z. You can see that this can be expanded to many matched pairs easily, while nested IF() statements become unwieldy very quickly.

[bradyso]

Oh ok, that seems to work pretty well so far. But what do I do if the list is really long? For instance in your scenario, I need to put 50 items in column Y, and get a different return for their counterpart to the right in column Z?

[acknak]

Sorry, I'm not seeing the problem there.

VLOOKUP will handle hundreds of items in the table; it's much simpler than a single formula with hundreds of IF statements, no?

[bradyso]

Yes you're right it most certainly is. But when I put something in N23 for instance, can Vlookup reference the list in column Y and insert the corresponding word to the right in column Z and place it in column O?

For instance, I want to paste a massive list into column N, and Vlookup looks for a match anywhere in column Y, and finds the correct counterpart in column Z and pastes it in column O.

Right now when I do that, it just says #N/A. I don't think I did a good job of describing what I'm trying to do...

[MrProgrammer]

Hi, and welcome to the forum.

I need multiple if then statements …

IF() is a function, not a statement. There are no statements in spreadsheet formulas.

But when I put something in N23 for instance, can Vlookup reference the list in column Y and insert the corresponding word to the right in column Z and place it in column O?

Yes, that's exactly what VLOOKUP does when you use it in column O.

Right now when I do that, it just says #N/A.

[Tutorial] VLOOKUP questions and answers
Read the entire tutorial, but for unexpected #N/A, see Q5, Q6, Q11, Q13, Q14, Q16, Q17, and especially Q19.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Villeroy]

Put this into a cell, say X3: =VLOOKUP($N3;$Y$1:$Z$2;2;0)
Copy the cell.
Select the target range, say X4:X9999
Paste.

[bradyso]

Solved, thanks guys I appreciate it.