# Lookup a value in a range & find the cell in another range for further use

#### hsandeep

##### Well-known Member
A6:L6 generates either 1 or 0 with a rule that ONLY 1 of the cell in the range (A6:L6) would be equal to 1.
A2:L2 also generates values.
I want to use the corresponding cell in the range A2:L2 for 1 generated in the A6:L6 for comparison with N2.
N2 also generates a value.
Output required: O2=If N2>=(cell from the range A2:L2) than 1000 else 2000.

How to accomplish.
Thanks
Sheet1

 A B C D E F G H I J K L M N O 1 Compare with Result 2 200 450 250 150 175 200 400 100 200 500 555 185 200 2000 3 4 5 6 0 0 1 0 0 0 0 0 0 0 0 0

<tbody>
</tbody>

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### cdchapman

##### Board Regular
Hello hsandeep,

Based on your explanation of what you want to achive, try the following formula in cell O2:

=IF(N2>=INDEX(A2:L2,MATCH(MAX(A6:L6),A6:L6,0)),1000,2000)

Regards Chris

#### hsandeep

##### Well-known Member
cdchapman

Your formula WORKS. But if I change a little.....A6:L6 generates +ve integers & want to match the cell for generation of value 5, then the formula bro?

#### cdchapman

##### Board Regular
Actually, thinking about it, I have made a slight mistake in the INDEX part of the original formula. Apologies - it should be:

=IF(N2>=INDEX(A2:L2,1,MATCH(MAX(A6:L6),A6:L6,0)),1000,2000)

In your original post you said that cells A6:L6 generate a 0 or 1, with only one of those cells containing 1. This is why I used the MAX function to pick out the column with the value of 1 (this being the maximum value in the range).

When you say '...want to match the cell for generation of value 5', do you mean that only one cell in the range A6:L6 will have the value 5, and all other cells in the range will any other integer values other than 5? Could these cells also have values of greater than 5?

If so, then this formula should work:

=IF(N2>=INDEX(A2:L2,1,MATCH(5,A6:L6,0)),1000,2000)

If my assuption above is not correct, please post a sample of your new data as per your original post, and I'll see what I can do for you.

Regards
Chris

#### hsandeep

##### Well-known Member

only one cell in the range A6:L6 will have the value 5
all other cells in the range will any other integer values other than 5
Could these cells also have values of greater than 5? YES
any change in your recent formula then?

#### cdchapman

##### Board Regular
Ok, as there is now the chance that the values in A6:L6 could contain values greater than 5, the MAX function can no longer be used. The revised formula is:

=IF(N2>=INDEX(A2:L2,1,MATCH(5,A6:L6,0)),1000,2000)

The MATCH part of this formula now returns the column number in the range A6:L6 that matches the value of 5 only, which is then used by the INDEX function to return the value in range A2:L2 in the corresponding column. If the value you need to search for changes from 5, just change the value in the above formula highlighted in red to the new value required.

Regards
Chris

#### hsandeep

##### Well-known Member
Please refer to my thread #1. I have 4 more columns P, Q, R & S.
P2=value of the cell which is 2 columns to the LEFT of the resultant cell, resultant cell is C2 in this case since C6 contains 1, provided the cell exists in the range A2:L2. Answer=200 (cell value of A2)
Q2=value of the cell which is 1 column to the LEFT of the resultant cell, resultant cell is C2 in this case since C6 contains 1, provided the cell exists in the range A2:L2. Answer=450 (cell value of B2)
R2=value of the cell which is 1 column to the RIGHT of the resultant cell, resultant cell is C2 in this case since C6 contains 1, provided the cell exists in the range A2:L2. Answer=150 (cell value of D2)
S2=value of the cell which is 2 column to the RIGHT of the resultant cell, resultant cell is C2 in this case since C6 contains 1, provided the cell exists in the range A2:L2. Answer=175 (cell value of E2)

HOW TO ACCOMPLISH ABOVE?

Replies
6
Views
64
Replies
5
Views
73
Replies
4
Views
94
Replies
11
Views
122
Replies
13
Views
133