Vlookup for multiple instances

[gammaves]

Hey everyone,

I've got a few problems I'm having using vlookups - I feel like there's gotta be something I'm not thinking of.

- Is it possible to get a lookup to find the Nth instance of a lookup value?
- Can I easily add together every instance?

Maybe this is a case for Match instead? Please take a look at the sample I've created and let me know?

(Also, if you want to see some of my spaghetti-formulas, take a look at B2 - I'm very open to criticism)

https://drive.google.com/file/d/1i5oj85KJkS7aeLQ922NaDeS4I-EtA5i2/view?usp=sharing


Thanks a lot!

 

[Special-K99]

I'm having to request access to download this file.
You might wanna change that if you can, I'll give it a short while then I'll be moving on to another problem.

 

[gammaves]

Sorry - I didn't realize the sharing settings. I gave you access. I'll change the file location now

 

[Special-K99]

Issue 1:
Ok, here's the best way to do this, it involves reformattiong your data a little

1. Insert a column in front of column A.
2. in A3:A9 put just the Site and numbers, e.g. Site1, Site1, Site2 etc
3. n B3:B9 put just the Sitename, e.g. 255808, 201275
4. Copy B23:B26 to A23:A26
5. in B23:B26 put just the Site and numbers as per step 2 overwriting the Company names (which are now in column A)

in C3
=SUMPRODUCT(($B$14:$B$19=$B3)*($C$14:$C$19=C$2)*(D$14:D$19))
in D3
=SUMPRODUCT(($B$14:$B$19=$B3)*($C$14:$C$19=D$2)*(E$14:E$19))
in E3
=SUMPRODUCT(($B$14:$B$19=$B3)*($C$14:$C$19=E$2)*($H$14:$H$19))
in F3
=SUMPRODUCT(($B$14:$B$19=$B3)*($C$14:$C$19=F$2)*(D$14:D$19))
in G3
=SUMPRODUCT(($B$14:$B$19=$B3)*($C$14:$C$19=G$2)*(E$14:E$19))
in H3
=SUMPRODUCT(($B$14:$B$19=$B3)*($C$14:$C$19=H$2)*($H$14:$H$19))
in I3
=IFERROR(VLOOKUP(A3&" - C"&RIGHT(I$2,1),C$23:D$26,2,0),"")

Issue 2:

There's no refund column for Company B so nowehere to display the results
1. Insert a column after column I, put refunds in I1, Company B in i2

in I3
=IFERROR(VLOOKUP(A3&" - C"&RIGHT(J$2,1),C$23:D$26,2,0),"")
and copy down the column

Issue 3:

Unsure about solving this.

and copy these down the columns

NOTE: There's only data for Site1 255808 and Site3 Company A refunds since none of the other Sitenames appear in the DataDump1 table