Forum Discussion
Multiple match and lookup
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
- 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)
=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.
=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.
