If string contains word from list, return value adjacent to list value

by

One method of a ‘reverse-wildcard’ lookup can be achieved is with the newer AGGREGATE¹ function. This function can produce cyclic calculation and has an option (e.g. 6) to discard errors. Use this to produce a row number on the match to the cross-reference table with the INDEX function returning the actual value.

      Reverse VLOOKUP

The formula in B3 is,

=INDEX(F$3:F$5, AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&E$3:E$5&"*", A3, 0)), 1))

Note that ROW(1:3) is the position within F3:F5, not the actual row number on the worksheet. I’ve also scrambled the Find and Insert values in your original cross-reference table to avoid the perception of an associative lookup match.

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

More Related Contents:

  1. Sequence of numbers based on start and end value of 2 cells [closed]
  2. Function to convert column number to letter?
  3. Read/Parse text file line by line in VBA
  4. Better way to find last used row
  5. Excel VBA: Answer gets “stuck”
  6. 2 Dimensional array from range [closed]
  7. filter out multiple criteria using excel vba
  8. Excel VBA autofilter all but three
  9. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  10. Using Google Chrome in Selenium VBA (Installation Steps)
  11. Dir() function not working in Mac Excel 2011 VBA
  12. Excel VBA code to copy a specific string to clipboard
  13. How to use workbook.saveas with automatic Overwrite
  14. How do you run a .exe with parameters using vba’s shell()?
  15. How to use FindWindow to find a visible or invisible window with a partial name in VBA
  16. How do you get a Range to return its Name?
  17. Passing variable from Form to Module in VBA
  18. Excel VBA: Convert a date string to a Unix timestamp
  19. Refreshing all the pivot tables in my excel workbook with a macro
  20. Countif With Multiple OR Criteria
  21. Vlookup to copy color of a cell
  22. excel vba freeze pane without select
  23. How to delete rows in Excel based on criteria using VBA?
  24. Converting a string representation of a constant into a constant?
  25. Macro to export MS Word tables to Excel sheets
  26. VBA WinHTTP to download file from password proteced https website
  27. Create folder path if does not exist (saving from VBA)
  28. Access files with long paths (over 260)
  29. Concatenate multiple ranges using vba
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment