Forum Discussion
Lookup values based on 2 different search criteria
- I am using Excel 2013
- I oversee multiple retail locations with various retail brands in each
- Some brands appear in more than one location
- I have surveyed all retailers across all locations in one survey
- the responses are beginning to come through, but I need to map the responses to each brand, in their correct location
- In my document, I have tab 1 which is where I will paste responses from the survey
- I need the location tabs to have a formula which will lookup the data from the survey responses and place the correct information against the correct retail brand in the correct location tab.
- The location tabs currently have all the retail brands listed with other key information applied against them and I want to build this new data in amongst the existing data - so I cant start a fresh sheet.
- I originally started to build using Index and Match function, but the issue is the retail brand name appears in multiple locations and so 'Match' has to correctly identify first the location and then also the retailer brand, which I am not sure how to create that functionality.
- I have been given some help for a formula in Google Sheets which works, but this doesn't transpose to Excel (I have pasted the sheets formula as an image in the attached)
- Looking for assistance to find a formula that can achieve the result in Excel.
5 Replies
First correct word Location in sheet Survey Response. Then you can use these formulas:
- Sheet Location 1, 2, 3
in cell F2
=INDEX('Survey Response'!$C$2:$C$100,MATCH(A2&B2,INDEX('Survey Response'!$A$2:$A$100&'Survey Response'!$B$2:$B$100,),0))
in cell G2
=INDEX('Survey Response'!$D$2:$D$100,MATCH(A2&B2,INDEX('Survey Response'!$A$2:$A$100&'Survey Response'!$B$2:$B$100,),0))
both data in column F and G format as Time.
in cell H2
=INDEX('Survey Response'!$D$2:$D$100,MATCH(A2&B2,INDEX('Survey Response'!$A$2:$A$100&'Survey Response'!$B$2:$B$100,),0))
You can download my file here: https://1drv.ms/x/s!Ah_p7p0xkR5RnXQWftJ48LS0sKso?e=nQCXZe .
Hope this helps.
Regards,
IlirU
- Thank you Ilir for your support
Please try this...
In F2
=INDEX('Survey Response'!C$2:C$34,MATCH(1,INDEX(('Survey Response'!$A$2:$A$34=$A2)*('Survey Response'!$B$2:$B$34=$B2),),0))and then copy it across and down.
- Thank you Subodh for your support
You're welcome Gareth_Durbridge! Glad I could help.
Please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.
