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

@D7d7ono 

=INDEX(Tabelle1!J6:J20,MATCH(1,(Tabelle1!H6:H20=Tabelle2!F7)*(Tabelle1!I6:I20=Tabelle2!Z4),0))

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. 

@D7d7ono 

=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)