Search code examples
excelexcel-formulaformula

Return a value from if there is match between 2 tables

First I'll describe scenario, I've 2 tables, 1st table with columns like Hsname, prod, ver & bundled (fill-up values using formulas) and 2nd table with columns like prod, ver and bundled. I've to fetch values from 2nd table bundled column to 1st table bundled column on exact match of prod & version in both tables. Below is the screenshot attached and formula used

=IF(AND(MATCH(B2,Sheet2!$A$1:$A$14,0),MATCH(Sheet1!C2,Sheet2!$B$1:$B$14,0)),Sheet2!C2:C14,"No")

note: I'm using old version of excel where I'm unable to use dynamic array.

table1

table2

Image 4

enter image description here

Solution

  • I'm a little bit out of the old-fashioned formulae, so maybe someone can come up with something cleaner, but here are my two cents:

    enter image description here

    Formula in D2

    =LOOKUP(2,1/(H$2:INDEX(H:H,SUMPRODUCT((F$2:F$14=B2)*(G$2:G$14=C2)*ROW(H$2:H$14)))<>""),H$2:H$15&"")
    • LOOKUP() will, as it were, auto-CSE this formula for you, where;
    • We concatenate each value in the lookup range with an empty string to prevent unwanted results (zeros);
    • A boolean structure is used to find the row number;
    • INDEX() is used to build a dynamic range of which LOOKUP() can find the last (and also 1st) value from our lookup range.

    Note that this works because the topleft cell of an merged range holds its value. Also note that merged cells are Excel's worst nightmare.

    Note that the lookup range is one cell past the range of column F:G, e.g.: H$2:H$15, for a purpose because it would throw an error instead of empty strings if the very last bundle is not a merged cell but a single cell on it's own.