VLOOKUP Maching

user_19896662 0
Viewed 32

enter image description here

  1. User inputs two comma separated lists - list A and list B.
  2. We take an element from the list A - and match it with the entire range.
  3. Whichever cell from list B has the highest match, will be return against the values of User inputs two comma separated lists - list A and list B.

Can anyone try to solve this, I tried to use VLOOKUP with different set of functions but it didn't resolved this query. Link to excel sheet: https://docs.google.com/spreadsheets/d/1--nD0Yq-MeKy0GNPEoXT_87AyPJAKr12gkiAPjOc5pk/edit?usp=sharing

excel
1 Answers

try:

=INDEX(SORT({MMULT(REGEXMATCH(TRIM(SPLIT(D$3:D$6, ",")), SUBSTITUTE(B3, ", ", "|"))*1, 
 SEQUENCE(COLUMNS(SPLIT(D$3:D$6, ",")), 1, 1, 0)), D$3:D$6}, 1, 0), 1, 2)

enter image description here

user_56326290
Related