Vlookup for items in a comma separated list
I have a table with different values associated with different name.
| Column A | Column B |
|---|---|
| height 1 | 2 |
| width 1 | 5 |
| height 2 | 8 |
| width 2 | 9 |
In another sheet, I have a table with multiple values separated by comma in each cell.
| Column A1 | Column B1 |
|---|---|
| height1, width2 | Sum??? |
| width2, height 2 | sum??? |
I would like to use Vlookup for each name separated by comma in Column A1 and get autosum for them in Column B1 using their values in Column B.
Please help.
I have multiple formulas but it doesn’t work
Solution
If you're on Windows, here's an example to try:
- Use
FILTERXMLto split by, - For
VLOOKUPwith exact matches (FALSEas the last parameter), we need to strip spaces in values - withSUBSTITUTE(…," ","") - Enter the formula in B8 and fill down as needed
--is used to convert text numbers (result ofSUBSTITUTE) to numbers
=SUM(--VLOOKUP(
FILTERXML("<x><h_w>" & SUBSTITUTE(SUBSTITUTE(A8," ", ""), ",", "</h_w><h_w>") & "</h_w></x>", "//h_w"),
SUBSTITUTE($A$2:$B$5, " ",""), 2,FALSE))
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table