1

I have issue with VLOOKUP function. Here is my sheets:

sheet1

id_1  id_2 count
28273 15   5
31866 19   4
2827  315  5
...

sheet2

id_1  id_2 count
2827  315  155
141   1064 555
...

Basically, I want to add count from sheet2 to respective row in sheet1. But I encountered a problem: In order to compare values, I concatenate them: so, for the first row in sheet1 I use id_1&id_2 in VLOOKUP value. The problem is that for 28273 and 15 from sheet1 I got 2827 and 315 from sheet2. So, it is not an exact match which I need.

What formula should I use to get count value from sheet2 for respective values, e.g.:

sheet1

id_1  id_2 count count2
2827  315  5     155
...
  • The last parameter should be FALSE for an exact lookup. Are you comparing the concatenated lookup value against a helper column with the values already concatenated? – fixer1234 Jan 14 at 11:42
  • It is FALSE. Problem is that row 2827 and 315 matching with 28273 and 15 since 3938315 is an exact match with 2827315 – Simon Osipov Jan 14 at 11:45
  • Yes, I tried comparing a concatenated column with another concatenated, faced this problem and would like to find a solution without concatenation or helper columns. – Simon Osipov Jan 14 at 11:52
  • LOL. Didn't even catch that detail about the two combinations being the same after concatenation. Add a period or some other symbol between them when you concatenate: A1&"."&B1. Do it for both the helper column and the lookup string. The period will ensure the string is unique. Concatenation and a helper column is the simplest solution. – fixer1234 Jan 14 at 11:57
  • It would be helpful if you posted the formula you already have. I believe the easiest solution would be to use an IF() statement. Just nest the VLOOKUP(), so IF(VLOOKUP(one);VLOOKUP(two);???) – Kevin Anthony Oppegaard Rose Jan 14 at 12:00
0

In your comment, you said that you would prefer to not use concatenation or a helper column. Here's a way to do that.

I used your example. Sheet2:

enter image description here

Sheet1:

enter image description here

I only put the formula in the cell with a match because I didn't know how you wanted to deal with any potential non-matches (you could wrap this in IFERROR and display a blank). I did test it on all of the rows, and it properly handles your row 1 condition (returns an error based on the limited data and no error handling provision). The formula in D4:

=INDEX(Sheet2!$C$2:$C$6,MATCH(1,(Sheet2!$A$2:$A$6=A4)*(Sheet2!$B$2:$B$6=B4),0))

Notice in the image that it is surrounded by curly braces. That's because this is an array formula and must be confirmed with Ctrl+Shift+Enter instead of just Enter.

INDEX pulls the matching count from Sheet2. It does a logical test that will be true only if both conditions are true, so it compares 1 (True) to the results for each row. It compares the Sheet2 column A value to the column A value in Sheet1 and the Sheet2 column B value to the column B value in Sheet1. If it finds a record where both the column A and B values match the A and B values of the current row, that's a match.

If you can have the condition where not every row in Sheet1 has a count to retrieve on Sheet2, the simple way to display a blank on Sheet1 in the Count2 column is to wrap the formula like this (still an array formula):

=IFERROR( INDEX(Sheet2!$C$2:$C$6,MATCH(1,(Sheet2!$A$2:$A$6=A4)*(Sheet2!$B$2:$B$6=B4),0)) ,"")
1

If the two key values create a unique key then you can use SUMIFS and skip the array formulas:

=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2)

This will return 0 for any that do not have a match on the key values, but you can use a custom format of #;#;;@ to display an empty cell when 0. Or #;#;"No Match";@ to return No Match when 0

  • I like your solution the best. :-) – fixer1234 Jan 15 at 4:52
0

The easiest solution was proposed within the comments to use some kind of unique symbol between columns to create helper column for both sheets, e.g.: id_1&"."&id_2

So, first one - I created helper columns for sheet2 as id_1&"."&id_2.

Then I have on sheet1 columns:

id_1 id_2 count1 count2
...

On sheet2:

id_1 id_2 helper count2
...

Then I compare columns via VLOOKUP in sheet1:count2: VLOOKUP(sheet1!id_1&"."&sheet1!id_2;sheet2!helper:count2;2;FALSE)

  • Hello Simon, its great that you found a solution. Please post the actual answer instead of the short comment you have. You can then mark your answer as an answer to the question so it is removed from the unanswered questions option. – Kevin Anthony Oppegaard Rose Jan 14 at 12:17
0

fixer1234's gave the best solution in the comments already which Simon posted.

I have a stupidly complicated solution that still works if you cannot add columns. It does require that the id's are each unique, if the same id occurs twice it won't work properly.

IF(MATCH(A2;Sheet2!A:A;0)=MATCH(B2;Sheet2!B:B;0);INDEX(Sheet2!C:C;MATCH(A2;Sheet2!A:A;0));0)

Explanation: INDEX and MATCH are somewhat like the components of a VLOOKUP. MATCH finds the rownumber and INDEX finds the value at that rownumber in the range you choose. I check if the rownumber of the first id_1 equals the rownumber of the first id_2 and if so I do what is basically a faster and more flexible VLOOKUP.

  • One caveat: if there can be multiple combinations of id_1 and id_2, this could return the wrong result. Depending on the record sequence, the first match for id_1 might not match the first match for id_2, so it would return an error even though there is an actual match later. And the potential for the id_1 (only) match for the index finding a different record than the first pair is precluded only because the first pair would produce an error first in that case. – fixer1234 Jan 14 at 13:19
0
=INDEX(SHEET2!C:C,aggregate(14,6,row(Sheet2!A$2:A$10)/((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)),1))

The above formula uses AGGREGATE which performs array like operations without actually being an array, at least for some of the functions like 14 and 15. The 14 tells AGGREGATE to sort the results from largest to smallest, 15 would sort from smallest to largest. The 6 tells AGGREGATE to ignore results that result in errors.

ROW(SHEET2!A$2:A$10)

This part gives AGGREGATE the row number that is currently being evaluated.

((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2))

This part is the two conditions which both need to be true in order to for AGGREGATE not to receive an error. The * acts like an AND function. when both or either one is false, the results winds up being 0 which causes a divide by 0 error. If both results are true, then it results in 1 and the row number is not modified by dividing by one. You get a list of filtered results that match your criteria.

The 1 tells AGGREGATE to return the 1 results from the sorted list. This means that with multiple rows that match your criteria the last row that match is returned for AGGREGATE function 14 and the the first row is returned for function 15.

Now that you have the row number, that can be dropped into a full column reference for INDEX to pull the information from the corresponding row of the column.

Because AGGREGATE uses array like calculations, full column references within the AGGREGATE function should be avoid to reduce extra calculations which could in turn bog down your system. Within the AGGREGATE function, The range should be limited to your data.

Adjust the ranges to suite your data.

You did not state what you wanted as a result when something was not found in the list. Currently it will return the an error. To tidy that up you can wrap the whole thing in an IFERROR function and make it look like the following:

=IFERROR(INDEX(Sheet2!C:C,AGGREGATE(14,6,ROW(Sheet2!A$2:A$10)/((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)),1)),"NO MATCH")

POC

poc2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.