vlookup using multiple columns (win XP, Office 2k)

Topic

New Reply

Hello again.

I’m trying to get a spreadsheet to return a single value, from column Z, where Column A matches Column X and Column B matches column Y.

I’ve used vlookup extensively in the past, it can’t seem to be able to find out how to make it do this, or even if it will.

I found a few posts referring to the use of vlookup and match, but they seem to be directed at finding an intersection in a matrix.

Can anyone help, or should I just give up and dump my data into Access and go from there?

Thanks in advance

Ian

Reply | Quote

Replies

Try this as an array formula (confirm with Ctrl+Shift+Enter):

=INDEX(Z1:Z1000,MIN(IF(A1:A1000&”|”&B1:B1000=X1:X1000&”|”&Y1:Y1000,ROW($1:$1000))))

or

=INDEX(Z1:Z1000,MIN(IF((A1:A1000=X1:X1000)*(B1:B1000=Y1:Y1000),ROW($1:$1000))))

Reply | Quote