Multiple match and lookup

Occasional Contributor



Sheet 1

Column h6:h20 (list of product numbers)

Column i6:i20 (list of customer numbers)

Column j6:j20 (list of prices)


Sheet 2

Cell f7 (a product number, that can be matched in Sheet1 h6:h20)

Cell z4 (a Customer Number, that can be matched in sheet1 i6:i20)


In sheet 2 I want a formula that looks up cells f7 and z4 (in sheet 2) and finds a match in sheet1 and then pulls through the price from (sheet1 J6:j20 range)




3 Replies



Maybe with this formula like in the attached file. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. 


=INDEX('Sheet 1'!$J$6:$J$20, MATCH(1, ('Sheet 1'!$H$6:$H$20=F7)*('Sheet 1'!$I$6:$I$20=Z4), 0))


Replace Sheet 1 with the actual name of that sheet.

If you don't have Microsoft 365 or Office 2021, you should confirm the formula with Ctrl+Shift+Enter.

The index and match worked fine on initial test, however I have written this within a jet report (which i have just found out does not like arrays)