0

https://docs.google.com/spreadsheets/d/13ZXDE2efDOSa2mI4GlzZDvFqB-eCfxtoFNJE73IniaU/edit

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.

2

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.

2
  • Trim(),I like. Would you mind elaborating on the purpose of “columns(A10:B)” in vlookup?
    – Tedinoz
    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
1

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.