Index and match array formula

You can use Named Ranges to do that for you. it would help if we could see Sheet1, to guide you in the setting up of these ranges. It is also a bit wasteful to select all columns, when you are returning an answer ONLY from D.

=INDEX(Sheet1!D:D,MATCH(A2&B2,Sheet1!A:A&Sheet1!B:B,0))

Also, that type of concatenation array can be very slow. There are better (non-array) alternatives that have syntax like this:

Formula:

Please Login or Register  to view this content.

You can try to adapt that to suit your needs. However it would be good if you would post your sheet.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

So, I have a sheet; but the numbers weren't very realistic, were they? they were all the same!!!! I put in some real numbers to make it work.

I set up 3 named ranges (CTRL F3) dynamically to adjust to the lengths of Columns 1, 2 and the result column from Sheet 1. If the drat do NOT begin on row 2, you will need to adjust each of the named ranges =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) to reflect the first row of data (first bold character) and the number of non-blank cells above that row (second bold character).

The formula then is simply:

=INDEX(Result,MATCH(1,INDEX((Col_1=A3)*(Col_2=B3),0),0)) copied down
..

For future reference, attach sample data that has the same layout as your real sheet and which also has realistic data.