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

[hsandeep]

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>

 

[cdchapman]

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]

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]

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]

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]

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]

Thanks bro.. for the help rendered.
Can you also look at my another post http://www.mrexcel.com/forum/excel-questions/806555-7th-8th-back-ground-color-cell.html
You may ask any queries if you have regarding the above post?

 

[hsandeep]

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?