# Formula to search if a value is in range of 2 columns

1. ## Formula to search if a value is in range of 2 columns

Let's say I have the following:

Column A Column B Column C

Orange 100 200
Black 201 300
Yellow 301 400

Now, I have a different worksheet that has a column of values. I want to search for each value in the ranges of Cols B and C and return the color that is in Col A.

For example is as follows and Col A is what I am searching for and want to derive Col B:

Column A Column B

157 Orange
188 Orange
255 Black

Is there a way I can do this?  Register To Reply

2. ## Re: Formula to search if a value is in range of 2 columns

I think something like:

=LOOKUP(A2,'Sheet1'!\$B\$1:\$B\$100,'Sheet1'!\$A\$1:\$A\$100)

where Sheet1, A1:B100 contains the original data ... (you don't need column C) and A2 is the cell containing the first lookup value (157)

Adjust ranges and sheetname to suit.  Register To Reply

3. ## Re: Formula to search if a value is in range of 2 columns

try this macro ``Please Login or Register  to view this content.``  Register To Reply