Issue looking up Matching data

Hi,

I am having problems with working out how to do a lookup / Match formula to specifically cross reference a row of numbers against each row of numbers in a data set, and to then return the values that match in the first row alongside that row of the data set. For example I would have 8 numbers in row 1, and a data set of 20 rows of 8 numbers starting at row 3. I want to do a formula that looks at the numbers in row 1 and if any of them show up in row 3, list each matching number in columns alongside the numbers in row 3. I have attached an example to further demonstrate what I am wanting to do.

Any help would be greatly appreciated!

Regards,


Bill

Do you want each matching number in a separate cell? Try this formula in J10

=IF(COLUMNS($J10:J10)>SUMPRODUCT(1-ISNA(MATCH($A3:$H3,$A$1:$H$1,0))),"", SMALL(IF(ISNUMBER(MATCH($A3:$H3,$A$1:$H$1,0)), $A3:$H3),COLUMNS($J10:J10)))

confirmed with CTRL+SHIFT+ENTER (it's an array formula) copied across and down to Q22

Thanks guys,

Tried to enter the array formula in cell J10. Have attached a revised example showing the set of numbers and explaining what I am trying to do (hopefully the zip file has attached correctly as said it had when I posted last time :-)

When I enter the formula into cell J10 it just shows zero, even after doing Cntrl+Shift + Enter.

Cheers

Bill

Sorry one more thing. In the example I have attached, in the second set of 20 rows of data, how would I apply conditional formatting so that for each cell in each row that matches a cell in row 1, turns green? Given there are 8 numbers to check for a match, I have found I can only do a conditional formatting on 3 numbers in each row. (That is, I do a CF for cell A3 to see if it matches A1, add second CF to A3 to see if matches B1, same again for A3 to see if matches C1. I then get stuck as I still have to match off A3 for all 8 cells in row 1 through to H1, yet the CF only lets you format 3 conditions).

Thanks
Bill

Hello Bill, I'll start with the conditional formatting because that's relatively easy. This is what I did on the attached:

Select range A3:H22 (starting at A3).

Format > Conditional formatting > "formula is" with formula

=MATCH(A3,$A$1:$H$1,0)

choose required format


I got the formula a little wrong, somehow I confused row 10 with column 10 so when I said formula should go in J10 and include COLUMNS($J10:J10) I really should have said J3 in all instances.

Anyway, I revised the formula a little too, this should go in J3 copied across and down to Q22

=IF(COLUMNS($J3:J3)>SUM(COUNTIF($A$1:$H$1,$A3:$H3)),"", SMALL(IF(COUNTIF($A$1:$H$1,$A3:$H3),$A3:$H3), COLUMNS($J3:J3)))

confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar.

Note: In row 3 number 2 appears twice so it's shown twice in J3 and K3. Formula could probably be revised to not show duplicates....all numbers are listed in ascending order from column J rightwards, not in the order shown in columns A to H, this could probably also be changed if it's a problem.....

See attached example

Hi daddylonglegs,

That is fantastic! Thanks so much for your help, I really appreciate it. I tell so many people about the value of this forum, as it is such a great place to learn things & the help is brilliant!

Have a great day,

Cheers
Bill

Hi daddylonglegs,

Was just working with the file and have another query I am hoping you might have the answer to. With the numbers that match showing up in K3:Q3, is it possible that they appear in a corresponding column rather than from smallest to largest.

For example in the attached, if no. 6 appears in G1, then it will show in corresponding column P1. If no. 8 shows up in H1, it then correspondingly shows in Q1. If 45 shows in D1, then it will show up in M1, etc.

I am sure there is a simple fix however requires a greater skill than mine!

Thanks

Bill

Hi,

Once you start working with these files and realise the possibility of formulas, the floodgates open!!

Is it possible to use an IF statement to return a text value based on the results of Conditional formatting in the cells in columns A - H? For example in the v2 file I attached in the last post, I added a second CF to show the cell as yellow if the numbers in A3:H3 matched the cells in G1 or H1 (getting very clever :-)

What I was hoping is that to the right of columns J - Q, I could return a text value based on the CF in the first 8 columns. The formula I wanted to build would check to see if 3 of the cells were green in columns A3: H3, and one or two also were yellow, in which case it would return the word "Five". If there were four cells green (and one or two may be yellow, however is not dependent on having yellow cells to return the value), the formula would return the word "Four". If there are 5 green cells (and again may have one or two yellow however not formula dependent), the value returned would be "Three". Next if there were 5 Green AND one or two must be Yellow, value returned would be "Two". Finally if there were 6 green cells in A3:H3 (and one or two may be yellow however not dependent), the value returned would be "One".

Might be a bit tricky however this would be really handy if it is possible!

Cheers
Bill

Originally Posted by danison

For example in the attached, if no. 6 appears in G1, then it will show in corresponding column P1. If no. 8 shows up in H1, it then correspondingly shows in Q1. If 45 shows in D1, then it will show up in M1, etc.

I presume you mean that if A1 has a match in A3:H3 show that value in J3, if B1 has a match in A3:H3 show that value in K3 and so on up to H1....and the same for rows 4 to 22?

That's actually a bit easier to do than the original formula I proposed, try just this in J3 copied across and down

=IF(ISNUMBER(MATCH(A$1,$A3:$H3,0)),A$1,"")

Still thinking about your second query.....

Yes that is correct thanks. That makes a lot of sense for what I am trying to do!

Daddylonglegs

Thanks heaps. It is working beautifully. I can go to bed tonight knowing I have learnt something today :-) Much appreciated.


Cheers
Bill