Comparing one cell to an entire column (SOLVED)

[knitterkuba]

I'm just wondering what would be an easy formula to use if I am trying to compare B1 to A1:A10 and then if there is a cell matching in the A column then I want B1 to show that value but if there isn't one then I want it to be left blank.

[watersev]

do you want to see the first match only? In order to use formula it should be in a different cell. See attachment, try to change A13 cell value

[knitterkuba]

I should have clarified a little better. First, all of my values are strings of roman letters. I have values in A1:A10. In cell B1 I have I have a value. I suppose that in a separate cell I want to compare B1 to the values in A1:A10 (which happen to be unique). If the B1 value happens to appear anywhere in A1:A10 then I want that value to remain in cell B1 but if it does not then I want cell B1 to be blank.

[Marcol]

Try this workbook

Change the value in D1

You could write your search string directly in the formula, this is shown in B3

Is this what you are after?

[Marcol]

Does this help?

In B1
=MATCH(A1,ROMAN(ROW($1:$5000)),0)
Enter with Ctrl+Shift+Enter
Then Drag/Fill Down

Or applied as shown in this workbook.

[mikerickson]

A formula in some other cell cannot change the value in B1.
Unless you want VB, you will be setting up a circular reference (error).

[knitterkuba]

I feel like we're getting really close here. This is what I got so far with all of your help,
=IF(MATCH(B2,A2:A12,0),B2,"")
This is a formula in C3. I have a bunch of letters in cells A2 through A12. This is roughly how it looks:
asd pol #N/A
sdf
dfg
fgh
ghj
hjk
jkl
uio
tyu
rty
ert

This formula works great if C3 matches to something, the problem is that if it does not then I want C3 to not display anything but instead it gives me #N/A. I really appreciate everybody's help, especially now during holidays. Happy holidays to all.

[Marcol]

Did you try the formula I gave you in Post #3?

Just enter your data in column A and adjust the formula to suit your range.

[knitterkuba]

I'm sorry I have not noticed it before. There are two formulas in there. The only difference between the two are the quotation marks, one has them and the other one does not. Which one applies better? Also, what is the difference between putting the quotation marks versus not putting them?

[Marcol]

Have a look at this work book for an explanation.

I would use the method in Cell B2 refering to D2.

If you use the method in Cell B5 you will need to change the formula every time you want a different lookup value.

My choice of data in the original worksheet may have confused you, my apologies.

[knitterkuba]

And that works just as I want it to :D Thanks!!