# Match mutliple values and then display text if found

1. ## Match mutliple values and then display text if found

I've got two worksheets with values in columns A through E in both. Each column is a different criteria (i.e. column A = Name, B = Color, etc..). What I'm trying to do is create a formula to put in column F of one of the sheets that will look for an exact match of all 5 columns that are in the same row. If it finds a match it will display nothing but if it doesn't find a match it will then display an X. So for example, if in row 25 of worksheet A it had values of:

Column A = John, Column B = Red, Column C = 3, Column D = 1, Column E = 07F

If there is an exact match in any row of worksheet B then the formula will display nothing. If there is no exact match in any row of worksheet B then the formula would display an X in the cell. Thanks in advance for any help.

2. Try adding an extra column to each sheet which contains the concatenated values of columns A to E.

e.g.

``Please Login or Register  to view this content.``
Now you just have to compare one column in each sheet and you could use something like a nested IF and COUNTIF function to display your values.

3. Try something like:

``Please Login or Register  to view this content.``
Where Sheet1!A1:F9 is the table to lookup against..

This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down

4. I'm not sure how I could use an IF function since the I need a formula that will search every row for a match

5. As always, thanks for your help!

6. Here's another way, doesn't require CTRL+SHIFT+ENTER.....

=IF(SUMPRODUCT((Sheet1!B\$1:B\$9=B2)*(Sheet1!C\$1:C\$9=C2)*(Sheet1!D\$1:D\$9=D2)*(Sheet1!E\$1:E\$9=E2)* (Sheet1!F\$1:F\$9=F2)),"","X")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1