Multiple columns/values with index/match in Excel

0
votes

I need to get multiple columns/rows/values from one tables that matches some conditions in excel.

This is an example table .

Table

I need to pull only values that matches n in H column.

I use this formula, and got correct result but only first match in a table.i need to populate all rows.

=INDEX(G13:H21,MATCH($C$29,H13:H21,0),{1,2})

Appreciate help

1
arraysexcelindexingmatch
Use a filter. Either the auto or advanced filters should do what you describe. – Ron Rosenfeld
Filter doesn't work for me in this case. – Sasa
Perhaps if you explain your problem better, and why a filter doesn't work, you can get a more useful suggestion. To help us help you better, suggest you read the HELP topics for How do I Ask a Good Question, and also How to create a Minimal, Complete, and Verifiable example. Then edit your question to provide more information. – Ron Rosenfeld
I think I was very clear about my post. I tried to use filters from various forums but my data resides in imported CSV that changes every day. If you give me an example how do you mean to accomplish that, I can try . Ty – Sasa
You never mentioned that the data source was CSV! What version of Excel? – Ron Rosenfeld

1 Answers

0
votes

I don't understand why you write that a filter does not work in your case.

enter image description here

After you select the from text/csv option, at the next screen, select Edit.

enter image description here

Select to Use First Row as Headers

Then, from the dropdown arrow for column 2, you can select the Filter option, and check the n box only.

You will now have a recorded query that you can use for subsequent iterations of the CSV file.

If you need to filter for multiple values, you can import the CSV without filtering, then execute multiple queries against the originally imported data.

EDIT: If you choose this method, you should update the external connection query first. Then a Refresh All will update the dependent queries. You could do this by selecting Refresh on Workbook Open just for that query; or selecting it for none of them, but doing a refresh for that external query upon Workbook Open.

Other options:

  • Duplicate the table and have different auto or advanced filters on each iteration.
  • you could use a formula. One problem is that you would have to adjust your formula after each import if the range to be examined is different. With your data table in A1:B9, and your filter value in G1, a formula might look like:
Product:  =IFERROR(INDEX(Sheet5!$A$2:$B$9,AGGREGATE(15,6,1/(1/((Sheet5!$B$2:$B$9=$G$1)*ROW(Sheet5!$A$2:$B$9))),ROWS($1:1))-ROW(Sheet5!$A$1:$B$1),1),"")

Validity: =IFERROR(INDEX(Sheet5!$A$2:$B$9,AGGREGATE(15,6,1/(1/((Sheet5!$B$2:$B$9=$G$1)*ROW(Sheet5!$A$2:$B$9))),ROWS($1:1))-ROW(Sheet5!$A$1:$B$1),2),"")

Then fill down as far as needed