Return a value from if there is match between 2 tables

user_6795617 0
Viewed 481

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

formula excel-formula excel
2 Answers

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.

user_97581940

I hope this is what you might be looking for,

• Formula used in cell D2

=IFERROR(INDEX(LOOKUP(ROW($I$2:$I$14),
ROW($I$2:$I$14)/($I$2:$I$14<>""),$I$2:$I$14),
MATCH(B2&C2,$G$2:$G$14&$H$2:$H$14,0)),"")

So let me explain a little bit on the above formula, how it works,

• First I have concatenated the Product & Version cells and used MATCH Function to find the position of each

MATCH(B2&C2,$G$2:$G$14&$H$2:$H$14,0)

• Next I have used a LOOKUP Function along with ROW Function to create an array of the bundles columns

LOOKUP(ROW($I$2:$I$14),ROW($I$2:$I$14)/($I$2:$I$14<>""),$I$2:$I$14)

ARRAY_OF_BUNDLES

• Lastly I am wrapping the all within an INDEX Function & IFERROR Function(To Ignore the errors)

=INDEX(LOOKUP(ROW($I$2:$I$14),
ROW($I$2:$I$14)/($I$2:$I$14<>""),$I$2:$I$14),
MATCH(B2&C2,$G$2:$G$14&$H$2:$H$14,0))

Hence we get our desired output as expected!

FORMULA_SOLUTION

EDIT

Using Helper Columns

With helper columns also it can be achieved. please refer the image below

• Formula used in Helper Column J

=LOOKUP(2,1/($I$2:I2<>""),$I$2:I2)

• Formula used in cell D2

=IFERROR(INDEX($J$2:$J$14,MATCH(1,(B2=$G$2:$G$14)*(C2=$H$2:$H$14),0)),"")

Array formulas need to be entered using CTRL + SHIFT + ENTER

And Fill Down !

HELPER

Edit

Without Helper same above Formula works, If you are not Excel 2021 Or O365 Users you need to press CTRL + SHIFT + ENTER

=IFERROR(INDEX(LOOKUP(ROW($J$2:$J$14),
ROW($J$2:$J$14)/($J$2:$J$14<>""),$J$2:$J$14),
MATCH(1,(B2=$H$2:$H$14)*(C2=$I$2:$I$14),0)),"")

-----------------------------------------------------------------------------------------------------

user_81625200
Related