Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Column

1. Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Column

I have a spreadsheet where I want to check several things

I'll try and keep it simple:

To start, I want to go the function to compare the value in cell A1 with the all the values in Column B.
If, for example, A1<any cell in Column B, let's say, in the example below, B3 and B4, then I want it look at the correspondent value in Column C, C3 and C4. Then I would want it to return the row in which it found the highest value in column C which, as stated above, has a corresponding value in B that was less than the value in A.

I'd like to put that formula beside cell A1, so that I can tell which row is, if you will, below budget but has the highest value.

Something like this:

A1 = 45

COL COL COL
ROW A B C
1 45 32.3 101
2 42.6 115
3 54.3 156
4 64.1 124
5 22.3 111

So, in this example, the result would be 3, since both B3 and B4 are greater than A1, but C3 is higher than C4.

Of course, columns B and C are pretty long in my real spreadsheet.

Thanks for any help!

2. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

HI Olives,

I see only one value in column C, see attached:-

for olives.xlsx

Regards,
DILIPandey
<click on below * if this helps>

3. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

[QUOTE=dilipandey;3120574]I see only one value in column C...

Hi didli! Here's the example spreadsheet. There are values in Column C now. I hadn't formatted it right on the forum. Looking forward to hearing from you or anyone with an idea! Thanks!

for olives.xlsx

4. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Ok, let me ask by steps.

How can I get the program, Excel, to look at Cell A1, compare A1 to all the cells in Column B and let me know which cells in Column B are greater than A1?

Next: Say there are 2 cells in Column B, for example, say B3 and B4, that are greater than A1. How can I then have Excel look at the adjacent column, Column C, for each of these cells, C3 and C4, compare their values and tell me C3 is higher than any other match?...

Now, if I put numbers from 1, 2, 3 etc... in Column A, Excel could return the value 3, telling me C3, the highest of the values whose adjacent cell in Column B is greater than A1, is on row 3... Can anyone help? A very simple spreadsheet is attached. Thanks!

for olives.xlsx

5. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Hi Olives,

See the attached files with my suggestions / remarks ..

for olives.xlsx

Regards,
DILIPandey
<click on below * if this helps>

6. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Originally Posted by dilipandey
See the attached file...
Wow! So far so good! Pretty nice!... I've made comments and clarified in the spreadsheet and attached it below... I'm impressed, dili...Thanks!

for olives.xlsx

7. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

C3 is 156 which is higher among C3 and C4
but you need 4 which is appearing against C4 .. why ?

Regards,
DILIPandey
<click on below * if this helps>

8. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Dili, I rushed out for a meeting yesterday and made a mistake I apologize, you're right, I do need 3, which appears against C3... I also apologize because I thought I had replied yesterday, but now I don't see my comment... Thanks!

9. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

HI Olives,

Try using below formula:-
Formula:
`Please Login or Register  to view this content.`

enter this using ctrl shift enter key combination.

See attached:- for olives(1).xlsx

Regards,
DILIPandey
<click on below * if this helps>

10. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Actually I have given 2 formula options in above attachment, choose whatever works for you

Regards,
DILIPandey
<click on below * if this helps>

11. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Wow, pretty cool... I'm in awe - ! - Thanks! I just looked at the spreadsheet and saw how easy you made it! I'll be setting up the whole file in the next few days... Can't wait to use your formulas! Nice!

12. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

you are welcome olives

Regards,
DILIPandey
<click on below * if this helps>

13. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Dili - Im trying to setup my whole spreadsheet using Option 1 in the spreadsheet you last sent me. I think the formula makes refernce to cells F3 and F4. I guess that means I have to always "create" cells F3 and F4 for Option 1 to work?

Here's the formula in Option 1:

=VLOOKUP(MAX(IF((\$B\$1:\$B\$5=F3)+(\$B\$1:\$B\$5=F4),\$C\$1:\$D\$5,"")),IF((\$B\$1:\$B\$5=F3)+(\$B\$1:\$B\$5=F4),\$C\$1:\$D\$5,""),2,0)

14. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

I know and thats why I gave option 2 as well

Regards,
DILIPandey
<click on below * if this helps>

15. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Originally Posted by dilipandey
... thats why I gave option 2 as well...
Ok, let me take this by steps then. Here's the formula in option 2:

=VLOOKUP(MAX(IF((\$B\$1:\$B\$5=IFERROR(INDEX(\$B\$1:\$B\$5,SMALL(IF(\$B\$1:\$B\$5>\$A\$1,ROW(\$B\$1:\$B\$5),""),ROW(\$A1))),""))+(\$B\$1:\$B\$5=IFERROR(INDEX(\$B\$1:\$B\$5,SMALL(IF(\$B\$1:\$B\$5>\$A\$1,ROW(\$B\$1:\$B\$5),""),ROW(\$A2))),"")),\$C\$1:\$D\$5,"")),IF((\$B\$1:\$B\$5=IFERROR(INDEX(\$B\$1:\$B\$5,SMALL(IF(\$B\$1:\$B\$5>\$A\$1,ROW(\$B\$1:\$B\$5),""),ROW(\$A1))),""))+(\$B\$1:\$B\$5=IFERROR(INDEX(\$B\$1:\$B\$5,SMALL(IF(\$B\$1:\$B\$5>\$A\$1,ROW(\$B\$1:\$B\$5),""),ROW(\$A2))),"")),\$C\$1:\$D\$5,""),2,0)

Since I have a whole columns worth of data in Column B, can I, and should I substitute all instances of \$B\$1:\$B\$5 above with \$B:\$B? or should I give it the whole range? If I have a column's worth of data in C, too, what other changes should I make? Whew! Mind boogling! Thanks!

16. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Hi olives,

If you give entire B column in reference like B:B, it will slow down the calculation.
Suggest you to to use fixed range if you have like \$B:\$B500 else you can use defined name as well here.

Regards,
DILIPandey
<click on below * if this helps>

17. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Are there any changes I have to make referring to the range in Column C?

18. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Oops, reposted unwittingly!

19. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

column C should start with row 1 as I entered in the formula.

Regards,
DILIPandey
<click on below * if this helps>

20. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

... thats why I gave option 2 as well...
Dilip - I'm still playing around with your formulas... Am I right that if I paste the formula in Option 2 that it gives me the 2nd highest value after it does its comparisons? Thanks! Let me know, please, 'cause in my example it worked out that way and I'm wondering if that's the case or just a coincidence! Thanks!

21. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

...I'm still playing around with your formulas...
Another question, what if I wanted the first check, in Column B, to be whether the data in each cell in Column is LESSER THAN, rather than greater than, the value in cell A1, would it be easy to make changes to the formula in Option 2 to do this? It would still seek the highest corresponding values in Column C... thanks!

O.

22. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

share the Excel workbook with your expected output as per your above posts.. thx

Regards,
DILIPandey
<click on below * if this helps>

23. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Here we go! Thanks!

for olives 2.xlsx

24. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

I used the formula in G3 to find the highest value in Column C from those whose corresponding value in Column B
G3 is blank and no values in column B

Regards,
DILIPandey
<click on below * if this helps>

25. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Originally Posted by dilipandey
G3 is blank and no values in column B...
I fixed it on the attached spreadsheet..... I moved so much stufff around I forgot to check the references to the appropriate cells... Here we go!

for olives 2.xlsx

26. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Still G3 is blank and no values in column B ... I can assume things here but I don't like ***-U-ME

Regards,
DILIPandey
<click on below * if this helps>

27. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Originally Posted by dilipandey
Still G3 is blank and no values in column B ...
Dilip I uploaded the wrong file. I had named my last spreadsheet 'forolives 2' but 2 files appeared in the download manager application and I chose the wrong one. I'm at work right now and don't know how to download the files to check which one is which, so, I uploaded both. I'm sorry. Would you look at both, please? I don't have the original files at work and don't know how to download the files to this computer from the attachment application. I hope you can look at them and check to see which one is the right one. Otherwise I can check when I get home and upload the right one. Thanks! So much!

[ATTACH=CONFIG]indexOf[/ATTACH]for olives 2.xlsxfor olives 2.xlsx

28. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Originally Posted by olives
... I can check when I get home and upload the right one. Thanks! So much!
Ok, this the right one. You won't find any values in Column B. It's blank until the desired outcomes section at the bottom of the spreadsheet. But I think all the references are right in my comments. Thanks!

for olives 2.xlsx

29. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Hi Olives,

see the attached file where all your desired outputs has been obtained using formulas:-
for olives 2.xlsx

Regards,
DILIPandey
<click on below * if this helps>

30. Re: Returning a Value From A Cell in OneColumn Having Checked for Conditions in Another Co

Originally Posted by dilipandey
... see the attached file...
I just downloaded it and will check it out tonight! Thanks, Dilip! I'm in heaven!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1