Match Formula: Wildcards and Listing results

[SamNewey]

Hi Guys,

This one might be an impossible task, but I will ask nonetheless.

I have a list of data (don't we all) that I am searching through using the match formula. This formula at the moment looks like this:
=MATCH(1,($C$2=Sheet1!B:B)*($C$3=Sheet1!C:C),0)

Where C2 is one search term and C3 is the other. This formula will likely expand as I add more terms but I am starting small at the moment.
The formula works great and returns the data I need, however there are two things I would like to do. The first one regards wildcards.

Say for example I wanted to omit the C3 search term altogether, without changing the formula by just typing a "*" in the search field, I know this works with single match formula, but I don't think it works with arrays multiplied together. Does anyone have any ideas about this?

Secondly, I want to copy this formula down, and have it return the next positive value. At the moment it only shows the number 6 (the positive search return) endlessly. Is this easily done, or will it require a reformulation?

Thanks for any help in advance,
Sam

[ChemistB]

Do you want it to return 6 (the row that is the match for your criteria) or do you want to return what is in column B or Col C in row 6?

Try this ARRAYED Formula
=INDEX(ROW(Sheet1!$B$1:$B1000), SMALL(IF(ISNUMBER(MATCH(1,($C$2=Sheet1!$B$1:$B$1000)*($C$3=Sheet1!$C$1:$C$1000),0)), ROW($B$1:$B$1000)), ROWS($A$1:$A1)))

Does that work for you?

[bebo021999]

Could you post a simple worksheet with desired results?

[SamNewey]

Do you want it to return 6 (the row that is the match for your criteria) or do you want to return what is in column B or Col C in row 6?

Try this ARRAYED Formula
=INDEX(ROW(Sheet1!$B$1:$B1000), SMALL(IF(ISNUMBER(MATCH(1,($C$2=Sheet1!$B$1:$B$1000)*($C$3=Sheet1!$C$1:$C$1000),0)), ROW($B$1:$B$1000)), ROWS($A$1:$A1)))

Does that work for you?

I would like it to return the number 6 for now, as in the row the match was found for the criteria. I will then decide how I use that data later as it will likely be referenced by other sheets.

Could you please explain your formula a bit? I was not able to make it function and it is a bit beyond my knowledge to work out why it didn't. I can get my head around how small works, but I do not understand all the 'ROW' functions you have used.

I will upload a sample workbook if you fancy a go at making it work on that I can possibly reverse engineer it.

Thanks

Could you post a simple worksheet with desired results?

Bebo, I will upload a sample sheet and in the next comment I will explain what I want it to do, if it isn't self explanatory enough on the sheet

[SamNewey]

Here is the attachment,

Due to my companies export policy I cannot give actual data out, however this perfectly replicates the sheet I am working on, down to cell references. The first sheet has 2 'search' criteria, by that I mean, the employee will know the size and the series of the item they are looking for. On sheet 2 they will input this data.

Sheet 2 then will display the row match, and below it a quick view at the data fields 'Data1, Data2, Data3...'

I will most likely create another sheet which will reference this second sheet so it is necessary for a clear row match number to be output.

The reason I am using a match function is that it will easily allow me to expand search criteria if necessary

[ChemistB]

In your example, there is only 1 unique result for each combination of size and series. I had thought that you wanted to return multiple results?

[SamNewey]

In your example, there is only 1 unique result for each combination of size and series. I had thought that you wanted to return multiple results?

Ahah! Right you are, sorry that is an error in the sample workbook. The actual one does infact have some duplicate values, for example if you were to duplicate each row of data between 1 and 5 times that is the amount we are talking. So there are 5 variants of each size. The problem will only increase once I add more search fields, so I would like the ability to display all records that match the search criteria, but I see no reason that if it works for 2 fields, it won't work for 5 or 50 (exaggeration of course)

[ChemistB]

Okay, in C7 copied to the right, this ARRAYED Formula

=IFERROR(SMALL(IF(($C$2=Sheet1!$B$1:$B$1000)*($C$3=Sheet1!$C$1:$C$1000), ROW($B$1:$B$1000)), COLUMNS($A$1:A$1)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Explaination of the formula
Starting with the IF statement and working out
IF(($C$2=Sheet1!$B$1:$B$1000)*($C$3=Sheet1!$C$1:$C$1000), ROW($B$1:$B$1000))
This logical argument returns an array of 0's and 1's depending on whether the row matches or not. 1 will return the true portion of the IF statement (ROW(B1:B1000)) which is the row number.
0 will return FALSE (as there is no false section). Since it's an Arrayed formula, Excel stores all this in memory. {False,False,False,False....14, False, ..., 74,...}


Next:
SMALL(IF(($C$2=Sheet1!$B$1:$B$1000)*($C$3=Sheet1!$C$1:$C$1000), ROW($B$1:$B$1000)), COLUMNS($A$1:A$1)))
COLUMNS returns the number of columns in the range $A$1:A$1 which is 1 which returns the smallest number In the stored array which would be the row of the first match

As you drag that formula to the right, COLUMNS($A$1:A$1) becomes COLUMNS($A$1:B$1)=2, COLUMNS($A$1:C$1) = 3 and so on. Thus returning the 2nd match, 3rd match and so on?

Clear as mud? Questions?

[SamNewey]

That was brilliant!

The formula works and I actually understand it all! (Now there's a surprise...)

I didn't understand that you were using the IF formula as an array, I didn't realise you could do the same thing as a match function like that so I was confusing myself over where the IFs came from.

The small is more useful than I thought as well, I didn't understand how you had dragged it down using the $A$1:A$1 without the absolute referencing on one letter so that it would adjust the range as it was dragged, very smart!

Thanks for your help,

Sam.