Timeline for Using Google sheets `QUERY` instead of `VLOOKUP` - function returning empty - why?
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 24, 2021 at 22:20 | comment | added | Erik Tyler | Regarding your third question (added in the comment thread somewhere above), as you say in that same comment, "...the idea with all Stack network sites is to create a Q&A style resource for others..." With that goal in mind, additional separate questions in comments does not allow for others to easily find answers. I've "bent the rules" a bit by supplying the formula directly above, but please do submit another post for each new or separate question from here. | |
| Nov 24, 2021 at 22:17 | comment | added | Erik Tyler |
As to your added second question (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 ? ), use this in C1 instead of the posted formula: =ArrayFormula({"TAX (PULLED FROM MERCHANT TABLE)"; IF(A3:A="",,IFERROR(VLOOKUP(A3:A&B3:B&COUNTIFS(A3:A&B3:B,A3:A&B3:B,ROW(A3:A),"<="&ROW(A3:A)),{E3:E&F3:F&COUNTIFS(E3:E&F3:F,E3:E&F3:F,ROW(E3:E),"<="&ROW(E3:E)),G3:G},2,FALSE)))})
|
|
| Nov 24, 2021 at 22:06 | history | bounty ended | sam | ||
| Nov 24, 2021 at 22:06 | vote | accept | sam | ||
| Nov 24, 2021 at 1:21 | comment | added | Erik Tyler | See the newly added Addendum to my post. | |
| Nov 24, 2021 at 1:21 | comment | added | Erik Tyler |
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.
|
|
| Nov 24, 2021 at 1:13 | history | edited | Erik Tyler | CC BY-SA 4.0 |
added 250 characters in body
|
| Nov 24, 2021 at 1:07 | history | edited | Erik Tyler | CC BY-SA 4.0 |
added 2502 characters in body
|
| Nov 23, 2021 at 22:03 | comment | added | sam |
"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)
|
|
| Nov 23, 2021 at 19:12 | comment | added | Erik Tyler | 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. | |
| Nov 23, 2021 at 19:03 | comment | added | Erik Tyler | 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. | |
| Nov 23, 2021 at 12:56 | comment | added | sam | 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 ? | |
| Nov 18, 2021 at 17:39 | history | answered | Erik Tyler | CC BY-SA 4.0 |