Vlookup or not

[GregM40]

I need Excel to return the value column D from Sheet1 when the values of columns B and C on both worksheets match.
I have manually entered the values in D4 and D5 to show what I am after.

Thank you for all your help

[ChemistB]

Most efficient way is with a dummy column on sheet1. In column A of sheet1, in A4 = B4&C4 copied all the way down. (You can hide this column if you wish)
Then in Sheet2, you can use this formula

=IFERROR(VLOOKUP(B4&C4,Sheet1!$A$4:$E$7142,4,FALSE),"")
Does that work for you?

You can also use array formulas and then you don't need the dummy column. However, depending on the amount of data you have, this might slow down your calculations.

For this you'd use
=IFERROR(INDEX(Sheet1!$D$4:$D$7142,MATCH(Sheet2!B29&Sheet2!C29,Sheet1!$B$4:$B$7142&Sheet1!$C$4:$C$7142,0)),"")
as an array (enter with CNTRL SHFT ENTER).
Hope that helps.

[GregM40]

ChemistB,

Thanks I was able to use the first formula you gave me. Not sure how to make the array formula to work. Where do I add the formula. I tried to add it on Sheet2 in D4 and Excel informed me that there was an error in the formula. I did enter the formula by entering it with CNTRL+SHFT+Enter.

[ChemistB]

I probably confused you by copying a formula from row 29. That would be modified for row 4. Attached is your spreadsheet with formula in place.