Split 1st column values to search two other columns and return single value

1
votes

I'm attempting to take a CONCATENATEd value in a column (A), SPLIT that value in two and VLOOKUP two columns in another sheet (same file). The issue I'm running into is if there are not two values to SPLIT in the CONCATENATEd cells, I get an error. I can flip my formula to search for two values or one (two values JOINed with : or a single value), but not both.

This is the latest iteration of my formula that's only showing half of the desired results.

=ArrayFormula(VLOOKUP(IF(FIND(":",A3:A),SPLIT(A3:A,":"),A3:A),{materialsData!C2:C,materialsData!D2:D,materialsData!E2:H},4,))

enter image description here Here is an example sheet of what I'm trying to do.

1
google-sheetssplitgoogle-sheets-formulaarray-formulasgs-vlookup

1 Answers

1
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A3:A, {IF(materialsData!D2:D<>"", 
 materialsData!C2:C&":"&materialsData!D2:D, materialsData!C2:C), materialsData!F2:F}, 2, 0)))