Sheet is on the cloud, and being used by people with Google Sheets, so VBA is out unfortunately.

Trying to get a vlookup to check 'Monday!A3' with 'Weekly Roster!E:E', but it also has to match 'Monday!E1' with 'Weekly Roster!G:G' and then returned 'Weekly Roster!H'

If this was VBA I'd be able to smash it out, but unfortunately its not!

Have tried numerous different ways, and they don't work! Here are some below examples;

=AND(VLOOKUP(E1,'Weekly Roster'!A:M,8,FALSE),VLOOKUP(A3,'Weekly Roster'!A:M,8,FALSE))
=VLOOKUP(A3, IF(E1='Weekly Roster'!G:G,'Weekly Roster'!H:H,""), 8, FALSE)

(And some others that have been deleted!)

If someone could point me in the right direction that'd be fantastic! Appreciate the help!

Sheet = Monday -> Matching 'Monday!A3' to 'Weekly Roster!E:E' and then also matching 'Monday!E1' to 'Weekly Roster!G:G' and returning 'Weekly Roster!H'

Monday Sheet:

Weekly Roster:

CodePudding user response：

Good Day,

Try this:

1. Add a new column in "Monday" Sheet at the beginning ("A" column and name it index) , Badge column will be on "B" now
2. In Cell A3 use: B3&"-"&$E$1 and fill it down for the rest of rows
3. Add a new column in "Weekly Roster" Sheet at the beginning ("A" column and name it index)
4. as the columns will be shifted now in Cell A2 use: F2&"-"&H2 and fill it down for the rest of rows
5. the Vlookup will be :

VLOOKUP(A3,'Weekly Roster'!A:N,9,FALSE)

CodePudding user response：

try like this in cell D2 in your Monday Sheet

=FILTER(FILTER('Weekly Roster'!E1:H3,('Weekly Roster'!G1:G3=$E$1)*('Weekly Roster'!E1:E3=A4)),{0,0,0,1})