Searching Data Table

[oldpcguy]

Hello all..!! I'm new to the forum but not new to spreadsheets. Sadly, while I was once considered extremely 'expert' with LOTUS 123... I'm now merely average with Excel.

Here is what I'm trying to do...

I have a table of data... 38 rows, 11 columns... numbers and text in the data field.

I have search criteria that comprise from 1 to 11 cells (in a row).

I'd like to find the "rows" in the data field that MOST match the data in the criteria (One or more numbers).

ie - if the criteria was 4 cells in a row containing 1, 5, 12, 20

I'd like to find ALL the rows that contain ONE of those numbers, TWO of the numbers, THREE, etc, up to ALL of the numbers. The criteria range would be a row containing one or more cells with data.

In the end, I'd like to copy the rows with matching numbers to a new location, and sort the results based on the rows containing the MOST numbers from the criteria.

For instance if row 1 had two of the criteria it would be ranked (sorted) below a row that had three of the criteria, etc.

Years ago I'd have used LOTUS's command language and done it all in a command language macro... but, that was 20 years ago.... and in LOTUS...(sigh).

Thanks for any help...

[oldpcguy]

Well... I came up with a quick and dirty solution.

First, using the HLOOKUP to determine if the criteria is "in" the data row...

Second, using ISERROR to determine if the result of above is "error" (criteria number is NOT in data row)

Third.. use IF/THEN to indicate that the criteria number IS in the data row... I don't need to know the number, just that the number is in the row.

Well.. then SUM to add how many numbers in the criteria are IN the data row... and SORT to rank everything...

Not real 'elegant' but works like a charm...

Solved..?? I suppose...