Forum Discussion
Looking for a way to use the filter function to search between two sheets.
I was wondering if it was possible to use the filter function to filter through two sheets: PURCHASES 1 and PURCHASES 2.
At the moment, I have a formula that reads like this:
=FILTER('PURCHASES 1'!$A$5:$M$5836,'PURCHASES 1'!$A$5:$A$5836=C3,"NONE FOUND")
I input a purchase code into cell C3, and it returns all of the purchase codes from PURCHASES 1 that match that cell.
I'm trying to change the formula so that it reads through PURCHASES 1 and PURCHASES 2 and returns all of the purchase codes from both sheets that match that cell (C3).
The formula that I've been trying to make work at the moment is:
=FILTER({ 'PURCHASES 1'!$A$5:$M$5836;'PURCHASES 2'!$A$5:$M$5836}, {'PURCHASES 1'!$A$5:$A$5836;'PURCHASES 2'!$A$5:$A$5836}=C3, "NONE FOUND")
However when I enter that formula, excel just tells me there is a problem with it.
I've tried using vlookup but I couldn't make that work either.
Is it possible to make this work? Or will I just have to create a separate sheet and filter for PURCHASES 2.
I'll attach an excel sheet link that demonstrates an example of how the exisiting formula works.
https://1drv.ms/x/s!AjW99mGTBmfug_gBbvTu6L30C_iyLA?e=Et4fQx
Thank you,
Harry
=FILTER(VSTACK('PURCHASES 1'!$A$5:$M$5836,'PURCHASES 2'!$A$5:$M$5836),VSTACK('PURCHASES 1'!$A$5:$A$5836,'PURCHASES 2'!$A$5:$A$5836)=C3,"NONE FOUND")
Does it work if you VSTACK the ranges?
2 Replies
Alternatively, you can use a combination of the IFERROR and VLOOKUP functions.
Here’s an example formula that you can use:
=IFERROR(VLOOKUP(C3,'PURCHASES 1'!$A$5:$M$5836,1,FALSE),IFERROR(VLOOKUP(C3,'PURCHASES 2'!$A$5:$M$5836,1,FALSE),"NONE FOUND"))
=FILTER(VSTACK('PURCHASES 1'!$A$5:$M$5836,'PURCHASES 2'!$A$5:$M$5836),VSTACK('PURCHASES 1'!$A$5:$A$5836,'PURCHASES 2'!$A$5:$A$5836)=C3,"NONE FOUND")
Does it work if you VSTACK the ranges?
