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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,826
Messages
5,855,866
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back