# Sheets - solution for a long nested if statement

I have done a lot of searching and reading and can’t seem to find a good solution to these long winded if statements.

In the range A2:A7 the data changes dynamically. Some of the names are not what they are suppose to be. They are always the same but always wrong.

Under the Names 1 list is the full list of names that will could dynamically come in to the items list. Next to them under Names 2 is what they should be.

In the cell B2 I’m using a nested if statement to compare each of the two Names Lists one by one and then copying that in each cell below. This is a long and complicated process with lots of items and then different categories too, the length of the lists change but never longer than 20 items.

Use `vlookup()` in cell `B2`, like this:

`=arrayformula( iferror( vlookup(trim(A2:A6), trim(A10:B), columns(A10:B), false) ) )`

The `trim()` is necessary because your data includes trailing space characters.

• Trim(),I like. Would you mind elaborating on the purpose of “columns(A10:B)” in vlookup? Nov 11 at 20:58
• The `vlookup()` function expects the number of columns in parameter 3. You can use the hard-coded value `2`, but that will fail in the event a column is inserted between column A and column B, while `columns(A10:B)` will adjust automatically, allowing the formula to continue working correctly. Nov 11 at 22:42

Move the Names1/Names2 data away from Column A and B. They could be on the same sheet or another sheet. In the following example, move them to cell H1.

Insert this formula in cell B2.

`=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,H2:I9,2,FALSE)))`

NB:

• this is a fairly inefficient formula since this is acting on every row in the sheet. So it would be best to delete as many unused rows as possible.
• "Red Grapes" in Names1 has a space at the end of the string; "Red Grapes" in Cell A4, has no equivalent space. I assume this is a typo

Sample

• While similar, the answer by @doubleunary includes at least two additional error-checking features. It is a better solution to the question. Nov 11 at 23:03