# 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?

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.

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.``