Warm tip: This article is reproduced from stackoverflow.com, please click
array-formulas google-sheets google-sheets-formula gs-vlookup split

How can I split cell values and compare the results with those of another cell?

发布于 2020-03-27 10:15:26

I have already asked a similar question in another thread which was wonderfully answered by someone. However, it seems like that even if it did help, it was not 100% ok for all my GSheets and different projects. So I have been able to get more useful data from my source and I think that I would be able to have a bulletproof solution if I am able to find the good formula...

So my data looks like that: enter image description here

  • Column A has a list separated by semicolons. Copy paste looks like that "Potato ; Banana ; Apple".
  • Column B has a list of IDs, which are linked to the data in column A. So Potato is ID 1871, Banana is ID 1890 and Apple is ID 1840. Copy paste of date is: "1871 ; 1890 ; 1840"
  • Column C should output formula with the value "Banana", because his ID is the highest of all (1890 > 1871 > 1840).

I have tried a lot of different things. Overall I tried to SPLIT the values with " ; " and create one array where I would try to sort them from the column or row where the IDs would be. I wasn't sure how to merge both results...

I tried to study the ARRAYFORMULA() function to see if it would help. gSheets is very nice to work with, but I come from a world of PHP where I would write 7 lines of code to achieve my goals, where here I need to do only 1 line of code and it is a challenge for me.

Any help is appreciated to find the formula in cell C1 that would get the value from A1 that has the highest ID in B1.

Questioner
Etdashou
Viewed
78
player0 2019-07-03 21:13
=ARRAYFORMULA(HLOOKUP(MAX(TRIM(SPLIT(B1, ";"))*1),
 {TRIM(SPLIT(B1, ";"))*1; TRIM(SPLIT(A1, ";"))}, 2, 0))

0