CODEWITHSUNDEEP
excelexcel-formula
Treevar
Treevar

Reputation: 27

Excel VLOOKUP or INDEX MATCH

Lets say I have 3 columns of data 

AMOUNT(A)  AMOUNT(B)     INVOICE(C)
55          49           4541
47          47           1515
42          47           4478
86          12           9993
12          100          1224
5           44           1452
            100          4287
            99           4444

What I need to check to see if the value in column A matches a value in column B. If there is a match, it will return the value from column C in column D. Now this is easy with a VLOOKUP however I am dealing with 700+ lines and there are multiple amounts that match. INDEX(MATCH) could work but it only returns one value. Is this a VB only problem?

I.E Value 47 exists in column B twice so therefore column D would return both invoice numbers (1515 - 4478)

Upvotes: 1

Views: 912

Answers (2)

hnk
hnk

Reputation: 2214

In VBA, the problem would be trivial as you have already correctly described the logic with the problem statement itself.

Using Excel functionality only, without VBA is the interesting problem. You need to do the following steps to achieve an output that looks like this...

enter image description here

The steps are as follows:

  1. Pivot Table: Make a pivot-table from the Columns (B) and (C) with Rows Fields as (B) and (C) and the minimum of AMOUNT(B) as the Value field. (See Figure below)
  2. Helper Columns: Make a column on the side of the pivot table, say (Q) which is simply equal to everything in column (P)

  3. Contd ... : In column (R) (which unfortunately happens to be in the sheet's column Q, sorry for the confusion there), as shown, for cell Q20 for e.g. put in the formula

    =IF(P21=P20,Q21&","&M20,M20)

  4. That will result in the creation of something like the table below:

Table:

(P) (Q)

12  9993
44  1452
47  4478,1515
47  4478
49  4541
99  4444
100 4287,1224
100 4287

Now the hard work is done. All you need to do is to lookup using VLOOKUP key-value pairs from this helper table (P)(Q) - It will be very fast because the pivot table always keeps it sorted and hence an exact LOOKUP is not necessary. - Screenshots are shown below for the Pivot table as well as for the final VLOOKUP formula

Pivot Table and Helper Table:

enter image description here

Final Formula:

enter image description here

Upvotes: 2

causita
causita

Reputation: 1708

so I'm using the Countif to see if there are dups based on column A. In the D column type, =IF(COUNTIF($A$2:$A$9,B2)>0,C2,"")

enter image description here

Upvotes: 1

Related Questions