Search column for three items

**erblaze** · 06-06-2011, 11:04 PM

I have a column B5:B26 and I would like to search it for R1, R2, and R3. If one of them is present in the column, I want that value in the cell. i.e. "R1" or "R2" or "R3". If there is no R1,R2, R3, then I would like nothing in the cell. The column will only ever have one of the three values or nothing. I have spent hours on this....

**Haseeb Avarakkan** · 06-06-2011, 11:16 PM

Hello, Try

=IFERROR(LOOKUP(9E+300,1/ISNUMBER(MATCH(R1:R3,B5:B26,0)),{"R1","R2","R3"}),"")

EDIT:

Just realize, you can use simply,

=IFERROR(LOOKUP(9E+300,MATCH(R1:R3,B5:B26,0),R1:R3),"")

**erblaze** · 06-07-2011, 10:57 AM

I did try the formulas. The first returns a ##. The second did not return anything to the cell when I entered a R1, R2, R3 to any of the cells in B5:B26.

**Haseeb Avarakkan** · 06-07-2011, 01:53 PM

Are you saying, you want to search "R1", "R2", "R3" values, not the values in R1, R2, R3 cells, if so tr this

=IFERROR(LOOKUP(9E+300,MATCH({"R1";"R2";"R3"},B5:B26,0),{"R1";"R2";"R3"}),"")

Could you please attach a dummy file? So we can see the data.

**erblaze** · 06-08-2011, 02:48 PM

That works. I broke down the original formula you gave me and came up with this:=IF(ISERROR(INDEX(B4:B25,MATCH("r?",B4:B25,0),1)),"",INDEX(B4:B25,MATCH("r?",B4:B25,0),1)) This seems to work also. Will this type cause problems? Your formula looks easier. Could you explain the Lookup portion. Why does the value have to be such a large number? I under stand the position returned by the Match formula. How does the array work in the result range? If this is too many questions is there a good book or site to read on this? I did attach a file of the problem. B38 was my solution, B39 your, B40 & B41 the progression to figuring this out. Thank you so much for all of your time.