1

I need to use use a "VLOOKUP" type formula to match values across two data sets, but instead of using 1 key value as you would normally with VLOOKUP in need to use 2 key values.

Please see below screenshot of example data. What I need to do is match the "TAX" value from the "Merchant Report" to the "Purchase Report" where "DATE" and "ORDER VALUE" match across the two data tables.

I use the term "VLOOKUP" in inverted commas, as i dont think this can be done with VLOOKUP (without a helper column), so instead was trying to use the following QUERY formula (learnt from this video) :

=QUERY($E$2:$G$12, "select G where F = """&B3&""" and E = """&A3&""" ", 0)

But it keeps returning "N/A - ERROR - Query completed with an empty output"

Ive made an example of the spreadsheet here in google sheets (please use file > make a copy if you would like to make a copy) - https://docs.google.com/spreadsheets/d/1VuVuSIiuLQLVrf5dhTwKV368pvrs-PyT4cnx7wLRdyE/edit#gid=0

A) Any idea why this isn't working ?

B) Any idea how i would deal with the lines colour flashed in yellow, where both the key values are the same, but the data to be matched differs ?

screenshot of example data tables

1
  • The first thing I notice is that you have two line items in "MERCHANG REPORT" with the same Date and Order Value, but a different "TAX" amount. This would make formula writing challenging, unless that is dummy data and you accidentally entered conflicting data.
    – Erik Tyler
    Nov 18, 2021 at 17:33

2 Answers 2

3
+100

See my comment directly below your post. Assuming that entry is not a possibility in your real data set (i.e., that two or more line items would have the same date and order value yet a different tax amount), delete everything from Col C (including the header) and place the following formula in C1:

=ArrayFormula({"TAX (PULLED FROM MERCHANT TABLE)"; IF(A3:A="",,IFERROR(VLOOKUP(A3:A&B3:B,{E3:E&F3:F,G3:G},2,FALSE)))})

This will generate the header and all results. You can change the header text within the formula itself.

VLOOKUP, as you can see here, is capable of using concatenated range data to find matches.

You'll need to format Col C as currency.

Addendum (after comments):

Apparently, part of the original goal was to know why the original QUERY formula used by the OP wasn't working. (This was not clear, given the opening statement: 'I need to use use a "VLOOKUP" type formula to match values across two data sets, but instead of using 1 key value as you would normally with VLOOKUP in need to use 2 key values.')

The OP's original QUERY formula that does not work:

=QUERY($E$2:$G$12, "select G where F = """&B3&""" and E = """&A3&""" ", 0)

The version of the above that will work:

=QUERY($E$2:$G$12, "select G where F = "&B3&" and E = date '"&TEXT(A3,"yyyy-mm-dd")&"'", 0)

The original formula doesn't work for a number of reasons:

1.) There are far too many quotes, used incorrectly, for the formula to have worked, even if the data to be matched were all strings.

2.) Quotation marks of any type are used to search strings. Typically, those are single quotes when used within the Select clause, which itself is already enclosed in strings.

3.) However, neither piece of data being compared is a string. One is a currency amount and the other is a date. Both are numbers, and each is treated differently. But neither would be enclosed directly within quotes in a QUERY.

4.) Numbers such as currency are simply searched, without being enclosed in quotes. So the dollar amount concatenation looks like this: "select G where F = "&B3...

5.) Dates within QUERY formulas must be set up a specific way; that is, they must be converted to text in a specific format that matches how SQL sees them. So the data-match concatenation looks like this: "select G where ... E = date '2021-11-23' " Notice the use of date and the single quotes within the Select clause that come before and after the date-string that will be formed by the TEXT function (which must be in the format yyyy-mm-dd only). Since your dates are variables, they must be interposed like this: "select G where ... E = date '"&TEXT(A3,"yyyy-mm-dd")&"'"

All of that said, using QUERY this way for the purposes in the original post would have required a separate QUERY formula per row. And then there is the added complication that a single date-amount combination may have more than one tax amount assigned to it, meaning that the QUERY for the first of those would return two rows of information, not one. That means that even if there were a separate QUERY dragged down into each cell of a column, this would result in an error at each point where the return would be more than one cell/row, because room would not have been left below each such QUERY for its expanded results.

All said, QUERY is the wrong function here. On the other hand, as I provided above, VLOOKUP is a concise, single-cell formula that can produce the results simply and without such conflicts.

8
  • Thanks Erik, that works great, if i wanted to add another condition to the formula to only have the array present a value for each row, if that row in column D of the purchase report had the string "amazon" in it, would that be possible ?
    – sam
    Nov 23, 2021 at 12:56
  • As you originally asked this question, did my posted solution solve that completely? (You've added more request to the original post since I posted my solution and are now asking for even more extended functionality.) If, in fact, my solution here met the originally posted goal, please take a moment to mark my post as "Best Answer." Once that is done, I'll take another look at the additional functionality you've now requested.
    – Erik Tyler
    Nov 23, 2021 at 19:03
  • In addition to my comment above, your linked sheet does not contain a column D at all, nor any reference to "amazon." Once you've completed the action requested in my previous comment, do make sure that your sheet reflects your real-world setup.
    – Erik Tyler
    Nov 23, 2021 at 19:12
  • "did my posted solution solve that completely" it was a solution to the problem, (which was very welcome and i appreciate your time in writing such a detailed response, hence i have upvoted it and being using it today) but in asking the question i was keen to understand why the QUERY formula approach wasnt working, hence the bounty applied to the question. As the idea with all Stack network sites is to create a Q&A style resource for others i thought this would be the correct thing to do in the circumstances. Apologies if i have offended you (that is meant sincerely)
    – sam
    Nov 23, 2021 at 22:03
  • It is not clear from your post that a primary goal was to have a full explanation of why a formula wasn't working. Yes, you added "Why isn't it working?" But in most cases here (as in real life), when someone asks "Why isn't this working?" within a greater context, they mean "How can I solve this?" Your opening paragraph was "I need to use use a "VLOOKUP" type formula to match values across two data sets, but instead of using 1 key value as you would normally with VLOOKUP in need to use 2 key values." The use of "VLOOKUP" type ruled out QUERY only in my mind from the start.
    – Erik Tyler
    Nov 24, 2021 at 1:21
1

Two different approaches

Got two different approaches, one is incomplete, the other one requires new office version.

Paste this in C3 and spread the formula to the rest of the column:

=index($G$3:$G$12,match(1,($F$3:$F$12=B3)*($E$3:$E$12=A3),0))

As for your second question, you would need to identify each line by using some form of ID, worst case scenario you can add an if statement which would make the formula three dimensional, checking the values in your first table and then checking all the previously checked values in your output table.

Formula from D3

=filter($G$3:$G$16,$E$3:$E$16=A3,$F$3:$F$16=B3)

For both formulas you can add as many extra conditions as you want.

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.