CODEWITHSUNDEEP
vbaexcel
Jeff Brady
Jeff Brady

Reputation: 1498

How can I find matching data in another worksheet and get a cell value?

I have an Excel workbook with 2 worksheets. Let's call them "Item List" and "Item Master".

In "Item List" I have 2 columns, B ("Code") and C ("Subcode"). It looks like this:

 A    B    C
----------------
100   AR
110   AR
120   NU
130   AR

In "Item Master" I have similar columns, K ("Code") and L ("Subcode"). It looks like this:

 E     K    L    
----------------
xx   100   AR
xx   100   AR
xy   120   NU
xc   120   AR
xz   130   AR

In "Item List", I need to have column C show the value of "Item Master" column E, IF the values of B and C match the values of K and L.

So if (Item List)A AND (Item List)B match (Item Master)K AND (Item Master)L, then (Item List)C = (Item Master)E

If there are multiple matches, the search can stop after the first match and get the value of column E since it will be the same for all matches.

How can this be done?

Upvotes: 1

Views: 35724

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

You can also use This array formula. You have to use CTL + SHIFT + ENTER after you enter the formula. This will negate the use of using a helper column

=INDEX('Item Master'!E:E,MATCH(1,('Item Master'!L:L=B1)*('Item Master'!K:K=A1),0))

The above formula goes in C1 of Item List You can then copy it down...

SCREENSHOT enter image description here

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27249

In the Item Master sheet, create the following formula in Column M =(K2&L2), then drag it down the rowset.

In column C of the Item List sheet write this formula.

=Offset(`Item Master`!$E$1,match($A2&$B2,'Item Master`!$M:$M,0)-1,0)

then fill down the row set.

Upvotes: 6

Related Questions