Google Sheet Arrayformula with Vlookup matching Data

0
votes

I hope you all doing great.

I have been using a an ArrayFormula along-with Vlookup to match the data from different sheet below formula is available in sheet2 Column with heading "Comment"

The problem is formula is not matching the all columns even not pasting the result that is available in Sheet1 Column "F".

Someone can please have a look on the problem to fix the bug/problem.

=ArrayFormula({"Comments";IF(B2:B="",,IFERROR(VLOOKUP(B2:B&" "&C2:C&" "&D2:D&" "&E2:E,SPLIT(TRANSPOSE(TRIM(QUERY(TRANSPOSE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/11R59FJN9oRkoh3TRNjf_lW_thkhRRxeslDjKRCP3Fhg/edit#gid=0","A:E")&IF(SEQUENCE(1,5)=4,"|",)),,5))),"| ",0),2,0),"*** NOT FOUND ***"))})

below are sheets link

https://docs.google.com/spreadsheets/d/11R59FJN9oRkoh3TRNjf_lW_thkhRRxeslDjKRCP3Fhg/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1yYEc5l5oZNIyMwCwfcYRuRxk_VBB70BUzgefDq6Kerw/edit?usp=sharing

If there is any easy way to do this with single formula then please share.

2
arraysgoogle-sheetsvlookupmatching
Sorry, I find it hard to understand: Can you please provide an example of the output you want to see from the formula? Can you also please make sure that you share TEST data and not real data. Thanks. – iansedano
It is simple as vlookup works, Vlookup (A2) matches the single cell whereas it matches the A2, B2,C3,D3 and E3 then will paste the "F" result from Sheet1 to Sheet2, Yes i have removed the necessary data. – Arham
Do you need this all to be in one single formula? Can't you first import your range to another sheet? Then create an ID column where you concatenate values and then perform an INDEX MATCH? – iansedano
Yes Sir, looking for a solution in just single formula. – Arham

2 Answers

0
votes

After going through your demo sheet, I came up with this workaround. I added a Query to take your lookup data range and have it built into columns. The example below worked for me.

=ArrayFormula(If(A2:A="",,ifError(VLOOKUP(A2:A&" "&B2:B&" "&C2:C&" "&D2:D,Query(SPLIT(TRANSPOSE(TRIM(QUERY(TRANSPOSE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/11R59FJN9oRkoh3TRNjf_lW_thkhRRxeslDjKRCP3Fhg/edit#gid=0","MCM!A:F")&IF(SEQUENCE(1,5)=4,"|",)),,5))),"| ",0),"Select Col1,Col2"),2,false),"*** NOT FOUND ***")))

Good luck!

1
votes

I think I've found your issue.

Your formula is not comparing equivalent "fields".
For the data from the importrange function, you do not include the MCM field in the first column. So when you compare values with your VLOOKUP, they never match.

You can test this by dropping the concatenation of column E in your VLOOKUP formula - change from this"

VLOOKUP(A2:A&" "&B2:B&" "&C2:C&" "&D2:D&" "&E2:E

to this:

VLOOKUP(A2:A&" "&B2:B&" "&C2:C&" "&D2:D

and then most records are matched.

Let me know if I've missed something here.

UPDATE: I removed an image I had showing the data alignment, but I have removed it, since the data may not be generic data.

UPDATE#2: I added a new tab, MCM-GK, in your sample Sheet2, and I have put a single formula in M1, to replace your formula in F1. However, it was not clear what result you want from the data when you do your VLOOKUP. I've assumed that you want the sixth column returned, which has status notes. Please let me know if this is what you want. If not, please explain clearly what data you want returned from the IMPORTRANGE from Sheet1.

Note that the hidden columns, G to L, show intermediate steps, just for clarification. They are not required by the final formula, and can be deleted when you want.