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

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
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

ABCDEFGHIJKLMNO
1Compare withResult
22004502501501752004001002005005551852002000
3
4
5
6001000000000

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Dec 30, 2010
Messages
112
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
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Dec 30, 2010
Messages
112
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
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Your assumption is CORRECT.
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
Joined
Dec 30, 2010
Messages
112
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
Joined
Dec 6, 2008
Messages
959
Office Version
  1. 2007
Platform
  1. Windows
Please refer to my thread #1. I have 4 more columns P, Q, R & S.
Answer required:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,058
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top