# Returning several matched values in a lookup

#### DavidVernon

##### Board Regular
Firstly thanks for reading this, people here are such huge helps

Right, here goes....

I have a list of cities in column A
In column C I have a complex formula that tells me if I have a problem in that city for a given criteria, e.g.:

=IF(Plymouth!B\$13=0,"no data",IF(INDEX(LINEST(OFFSET(Plymouth!B\$13,1-\$B\$4,0,\$B\$4,1)),1)<(-Plymouth!B\$13*\$B\$5),"problem","fine"))

This returns the values of 'problem', 'fine' or 'no data' and works excellently. Column C refers to a metric (call it metric A) and I have similar formulas in subsequent columns (for metrics B,C,D etc).

I am then left with a fantastic table that shows me all the cities in column A and then their status for each metric. Great. But...

I have a load of cities and a load of metrics. I would like to only show the
Cities where I have problems. I can use the autofilter and do it metric by metric, but this means selecting 'problem' in one filter box, writing down the cities, deselecting 'problem', selecting 'problem' in the next filter box etc. This is v.time consuming.

I would like to be able to interrogate my data in some way that I am left with a table that only shows me the cities that have problems.

For example, the resulting table might look like this:

MetricA MetricB MetricC etc
City1 City5 City16
City24 City15 City60
City43 City49
etc

Also, there may be no cities with problems for a particular metric.

Any suggestions?

David

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
A scaled-down sample of your (fake) data would help...

You could add another column of data to check is there is a problem for one of the metrics. Than sort according to this criteria and you will have the list of cities with problems.

Than sorting by each metric for only this small sample will be much quicker.

Here is a sample of my (now real) data..
Weekly Trend Monitoring.xls
ABCDEFGHI
7CountryBranchPresstogotosheetORDERINTAKEPRODUCTION
8xyzabStock
9numberm2tonnesnumberm2Value
10
11PolandBialystokfinenodatanodatafinenodatanodata
12PolandBydgoszczfinenodatanodatafinenodatanodata
13PolandWroclawfinenodatanodatafinenodatanodata
14PolandSkierniewicefineproblemnodatafineproblemnodata
15PolandKrakowproblemnodatanodatafinenodatanodata
16PolandSzczecinfineproblemnodatafinefinenodata
Issue Sheet
[/img]

the sheet has many columns and rows..

David

Here is a sample of my (now real) data..

[....]

the sheet has many columns and rows..

David
Book1
ABCD
1
2Order Intake
3120
4Numberm2tonnes
5KrakowSkierniewice
6 Szczecin
7
Sheet2

A3, copied across:

=COUNTIF('Issue Sheet'!D\$11:D\$16,"problem")

A5:

=IF(ROWS(A\$5:A5)<=A\$3,INDEX('Issue Sheet'!\$B\$11:\$B\$16,SMALL(IF('Issue Sheet'!D\$11:D\$16="problem",ROW('Issue Sheet'!D\$11:D\$16)-ROW('Issue Sheet'!D\$11)+1),ROWS(A\$5:A5))),"")

which is confirmed with control+shift+enter (not just with enter) then copied across and down.

Thank you so much - a much needed high to end a very dismal day...

Replies
6
Views
467
Replies
5
Views
125
Replies
1
Views
456
Replies
6
Views
213
Replies
4
Views
215

1,211,685
Messages
6,103,291
Members
447,853
Latest member
olddutch7

### 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.

### Which adblocker are you using?

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

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