INDEX returns the value at a position; MATCH finds that position. Together they look up values left or right of the key, without VLOOKUP's column-count limitation.
Do it with AI insteadSet the return column with INDEX
Start with =INDEX(return_column, ...).
Find the row with MATCH
Use MATCH(lookup_value, lookup_column, 0) for an exact match position.
Combine them
Nest MATCH inside INDEX as the row number.
=INDEX(D:D, MATCH(A2, B:B, 0))It can look left, doesn't break when columns are inserted, and is faster on big sheets. XLOOKUP now covers most of these too.
It asks for an exact match. Leaving it out makes MATCH assume sorted data and can return the wrong row.
Multiply the tests inside MATCH: =INDEX(C2:C100, MATCH(1, (A2:A100=F1)*(B2:B100=F2), 0)). In older versions confirm with Ctrl+Shift+Enter.
Use two MATCH functions: =INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0)).