Index Match Multiple criteia Giving zero value

lauren11

New Member
Joined
Jul 4, 2019
Messages
1
Code:
<code>=INDEX('Search Console Data'!$D$2:$D$76140,SMALL(IF(ISNUMBER(MATCH('Search Console Data'!$C$2:$C$76140,Sheet1!$B$2,0)),MATCH(ROW('Search Console Data'!$C$2:$C$76140),ROW('Search Console Data'!$C$2:$C$76140)),""),ROWS($A$2:A2)))</code>

The above code is for single criteria works awesome but the problem is multiple criteria, from the above code I am getting the values that matched with the cell value "Sheet1!$B$2" but along with that I need to match the other value in different sheet 'Search Console Data'!$b$1:$b$100="USA". How can I apply multiple criteria in the above formulae. Thanks for the help

With my knowledge I tried this (Sheet1!$B$2)*( 'Search Console Data'!$b$1:$b$100="USA") but it seems not working. Any idea?

I have attached the file, as per the file I want to return only if the page=/123/ and country=USA. Please see the sheet1 for the work I did. In the attached file I created single criteria formulae how can I update the formulae to multiple criteria

https://docs.google.com/spreadsheets/d/1SVLOVxg23umsioSWUmzacpT8yzu1g3XVPuCn5sFnp8Q/edit?usp=sharing
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hello,

Do you mean that within your Sheet1 ...

1. you are about to insert a new column (say in Column A ) ... to show the country ...

2. you need your current formula to be amended to include the country as an additional criteria
 
Upvote 0
As James asked, I am not sure if you want to keep your formula and update for multiple criteria. However, I used another formula to apply multiple criteria.

Enter the formula as array formula using Shift + Cont + Enter. You can include more criteria in ($C$2:$C$8=Sheet1!$B$2)*($B$2:$B$8="USA") part of the formula by using *. Note that I hard coded "USA".


=INDEX($D$2:$D$8,SMALL(IF((($C$2:$C$8=Sheet1!$B$2)*($B$2:$B$8="USA")),ROW('Search Console Data'!$2:$8),""),ROW('Search Console Data'!D1)))

Kind regards

Saba
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top