Capturing values with multiple conditions in excel

Question

Below is the link to a Google Sheets file that I need your help with: https://drive.google.com/file/d/1ejSffpo56lGFTRHTmueysF3WFZOIP2dO/view?usp=sharing

There are 2 spreadsheets in this file, the sales spreadsheet and the goal spreadsheet.

What I need is to simply pick up the target that the seller hit according to each seller and each category.

I don’t know much about excel and macros, and I can’t find the right words to look for a solution on the Internet, so I’m counting on you!!

Thank you very much!

Answer 1

I don’t know if I understood exactly what you want, but I could do something like this insert in sales in the E column for example the function:

=filter(metas!A3:F21;metas!A3:A21=A3;metas!B3:B21=B3;metas!C3:C21<=C3)

to reference other sheets in google Sheets is used:

Sheet name (or page) followed by character !

in your case, metas!, that way when you do something like metas!A3:F21 tera the return of cells from column A3 to F21 in the first sheet "goals"

in the function filter it takes several arguments, and each one is separated by ;.

• the first past indicates the return you want. in our case the return will be the spreadsheet lines sales.
• the following arguments indicate the filter criteria:
• goals! A3:A21=A3 means that sellers' names should be equal on both sheets
• the following filters the category
• and the following Verica if the target is less than or equal to sale

this in google Sheets, in excel I think only changes that instead of the ; would be , I don’t know if in excel it accepts multiple parameters.