Lookup with specific criteria

1_hunnid_dollars

Hey all

Having a bit of a problem with a formula I'm trying to recreate from Excel. I need to lookup an ID from Sheet 1 against Sheet 2 and return column 20 if column 4 meets the correct string criteria. Index/match with an IF? Index/Collect?

Help would be hugely appreciated!

Edit: I'm getting an incorrect argument error for my formula

=INDEX({Subject Status}, COLLECT({All Blocks Reg Report}, {Student ID}, [Student ID]@row, {Academic Period}, "Spring"), 0)

Further edit: Figured out the issue - =INDEX(COLLECT({Subject Status}, {Student ID}, [Student ID]@row, {Academic Period}, "Spring"), 1)

Cheers

NickStaffordPM

So you should be able to use a classic VLOOKUP for this.. maybe I am misunderstanding you though

=VLOOKUP(Lookup ID@row, Sheet 2 Range,16,FALSE)

Lookup ID @row = Lookup ID on Sheet 1 (where the answer will be populated)

Sheet 2 Range = Rows 4 through 20

17 = Because Column is the 17 row in that range, and that is the column we want the answer from.

FALSE = exact match

Let me know if this does not help!