Vlookup based on a Cell Condition

[fapb48]

Hi,

I wonder if this is possible to achieve.

I have a sheet with information since week 1 of the year.

And i am trying on a separate sheet to do a vlookup to separate the info for each week.

Example.

First sheet (spaces indicate a different column)

Name Week Number Delivery Success Customer Feedback Proof of Delivery Compliance
Anthony Baker WK1 99.9% 89.90% 90%
Daniel Nespral WK1 99.9% 88.0% 100%
Anthony Baker WK2 99.99% 100% 100%

And so on..

Example Screenshot:

(ignore conditional formatting)


On the other sheet that i am trying to Vlook has the following

Name of the person is within a dropdown range data validation
WK1 (static field)
Delivery Sucess
Customer Feedback
Proof of Delivery Compliance

And i want to extract all the info for Week 1 only.

Screenshot:

I have tried the following but it spills
=IF(Sheet1!B:B=C2,VLOOKUP(A2,Sheet1!A2:F6,2,FALSE)," ")

Is this possible at? Hopefully it makes sense

 

[Jeffrey Mahoney]

I created a rough version for you

FAPB48 20230808.xlsm
	B	C	D	E	F	G	H	I	J
2							Filtered List of Names		Unique Weeks
3	Driver Name:	Daniel Nespral	Week:	WK1			Anthony Baker		WK1
4							Daniel Nespral		WK2
5		Delivery Success	99.9%				Kathy Nobel
6		Customer Feeback	88.0%
7		Delivery Compliance	100.0%
Sheet3

Cell Formulas
Range	Formula
H3:H5	H3	=FILTER(UNIQUE(Sheet1!$A$2:$A$15),UNIQUE(Sheet1!$A$2:$A$15)<>"","")
J3:J4	J3	=FILTER(UNIQUE(Sheet1!$B$2:$B$15),UNIQUE(Sheet1!$B$2:$B$15)<>"","")
D5	D5	=SUMIFS(Sheet1!$C$2:$C$15,Sheet1!$A$2:$A$15,Sheet3!$C$3,Sheet1!$B$2:$B$15,Sheet3!$E$3)
D6	D6	=SUMIFS(Sheet1!$D$2:$D$15,Sheet1!$A$2:$A$15,Sheet3!$C$3,Sheet1!$B$2:$B$15,Sheet3!$E$3)
D7	D7	=SUMIFS(Sheet1!$E$2:$E$15,Sheet1!$A$2:$A$15,Sheet3!$C$3,Sheet1!$B$2:$B$15,Sheet3!$E$3)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C3	List	=$H$3#
E3	List	=$J$3#

FAPB48 20230808.xlsm
	A	B	C	D	E
1	Driver Name	WK	Delivery Success	Customer Feedback	Proof of Delivery Compliance
2	Anthony Baker	WK1	99.9%	89.9%	90.0%
3	Daniel Nespral	WK1	99.9%	88.0%	100.0%
4	Anthony Baker	WK2	100.0%	100.0%	100.0%
5	Kathy Nobel	WK1	95.0%	90.0%	100.0%
6	Daniel Nespral	WK2	98.0%	97.0%	99.0%
Sheet1

 

[fapb48]

That worked perfectly. thank you very much

 

[Jeffrey Mahoney]

You're welcome. Can you please mark my post as the solution.