0

Sorry for the noob question.

In Excel 2013, I have three columns.

Column A has the data that I would like to search for in Column B.

Column B contains a list of data.

Column C contains the data that states whether the data in Column B is of 'Yes' value or 'No' value.

My question is this: Is there a function or a way in Excel that can search for the data in Column B with the data in Column A as the search criteria?

The data in Column A is already in Column B, I just need to know what the data in Column C is.

ColA    ColB    ColC
6712XD  6700XD  Yes
6713XD  6701XD  Yes
6714XD  6702XD  Yes
         ....   Yes
        6712XD  Yes
        6713XD  Yes
        6714XD  Yes

Any help is much appreciated, let me know if I need to provide any more info.

howzat
  • 49
  • 1
  • 2
  • 12
  • Can you give an example of your data? – udden2903 Jul 18 '16 at 12:02
  • 1
    To answer your first question "Yes". To answer your 2nd question, Please read the HELP topics for [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and also [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) – Ron Rosenfeld Jul 18 '16 at 12:05
  • ColA ColB ColC 6712XD 6700XD Yes 6713XD 6701XD Yes 6714XD 6702XD Yes – howzat Jul 18 '16 at 12:05
  • @howzat Instead of putting data into comments, edit your question so as to include it in the body of the question. Also look at the links I posted previously so that you add the other helpful information. – Ron Rosenfeld Jul 18 '16 at 12:06
  • 1
    @howzat Please [edit](http://meta.stackexchange.com/a/120419/326794) your above post and include the sample data there in a structured / usable format. – Ralph Jul 18 '16 at 12:07
  • I'm still confused after you've posted the data. Are you essentially saying you want that row of Column C to be a VLOOKUP of whether that row of Column A exists in all of Column B? – Tim Edwards Jul 18 '16 at 12:22
  • @TimEdwards - Yes, although, when I tried the Vlookup option, it didn't work. By the way, none of the columns are in a table, which is why VLOOKUP does not work. – howzat Jul 18 '16 at 12:57
  • @RonRosenfeld - sorry for the confusion, I have updated the post – howzat Jul 18 '16 at 13:05
  • @TimEdwards - The whole of Column A will be in Column B, it's just that Column B is full of data (about 1500 entries) and Column A is only about 20 entries. The current method I use is to use the Find command (CTRL + F) and search for each piece of data from Column A in Column B – howzat Jul 18 '16 at 13:08
  • Still not quite clear on why you've said you can't use VLOOKUP. Are the columns in separate sheets / workbooks etc? Not that that would be a problem either mind you. – Tim Edwards Jul 18 '16 at 13:13
  • Yes, in seperate workbooks. So I have to copy and paste each cell into this workbook and then do CTRL+F – howzat Jul 18 '16 at 13:14
  • 1
    @howzat `VLOOKUP` will work on other workbooks. And it doesn't really matter if the data is in a `TABLE` or merely in a column of cells. So if that function is not working, you are doing something else to mess it up. – Ron Rosenfeld Jul 18 '16 at 13:24
  • `VLOOKUP` or `MATCH` or `COUNTIF` should all allow you to test for whether value exists. Can you show the formula you have tried to implement? (You will need to qualify the Workbook/Worksheet names if you're searching from multiple workbooks) – David Zemens Jul 18 '16 at 13:26
  • @DavidZemens - I am using the following formula: =VLOOKUP(A2,B2:B1349,7,FALSE) – howzat Jul 18 '16 at 13:39
  • As you were asked to do with your code, please put the *formula* in your question, and also describe what's *wrong* about the output from this formula. – David Zemens Jul 18 '16 at 13:44

2 Answers2

1

You can try this formula in cell D2:

= IF( COUNTIF( B:B, A2 ), C2, "" )

COUNTIF( B:B, A2 ) returns a number of how many times the value of cell A2 is found in column B, C2 is the result value if found, and
"" is the result value if not found.

Slai
  • 22,144
  • 5
  • 45
  • 53
  • Thanks for the answer. Unfortunately this does not work, it returns the answer as FALSE, but when I search the data with CTRL+F, it shows as 'Yes'. – howzat Jul 18 '16 at 13:36
  • ... then the value of C2 is actually TRUE and just displayed as Yes. You can try `= IF( COUNTIF( B:B, A2 ), IF( C2, "Yes", "No"), "" )` – Slai Jul 18 '16 at 14:07
0

Found the answer from Lookup using table with separated columns, it's:

=INDEX(AF:AF,MATCH(P930,Q:Q,0))

Thanks for your help :)

Community
  • 1
  • 1
howzat
  • 49
  • 1
  • 2
  • 12