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

**olives** · 02-11-2013, 02:08 PM

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!

**dilipandey** · 02-11-2013, 02:47 PM

HI Olives,

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

for olives.xlsx

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

**olives** · 02-11-2013, 03:13 PM

[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

**olives** · 02-11-2013, 06:53 PM

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

**dilipandey** · 02-12-2013, 01:21 AM

Hi Olives,

See the attached files with my suggestions / remarks ..

for olives.xlsx

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

**olives** · 02-12-2013, 01:07 PM

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

**dilipandey** · 02-12-2013, 01:53 PM

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>

**olives** · 02-13-2013, 01:57 PM

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!

**dilipandey** · 02-13-2013, 02:14 PM

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>

**dilipandey** · 02-13-2013, 02:15 PM

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

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

**olives** · 02-13-2013, 07:40 PM

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!

**dilipandey** · 02-14-2013, 12:52 AM

you are welcome olives

Please mark this thread as [SOLVED]... Thanks.

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

**olives** · 02-14-2013, 12:14 PM

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)

**dilipandey** · 02-14-2013, 01:43 PM

I know and thats why I gave option 2 as well

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

**olives** · 02-14-2013, 02:16 PM

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!

**dilipandey** · 02-14-2013, 02:24 PM

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>

**olives** · 02-14-2013, 03:17 PM

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

**olives** · 02-14-2013, 03:25 PM

Oops, reposted unwittingly!

**dilipandey** · 02-15-2013, 02:11 AM

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

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

**olives** · 02-20-2013, 01:47 PM

... 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!

**olives** · 02-20-2013, 01:52 PM

...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.

**dilipandey** · 02-20-2013, 02:00 PM

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

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

**olives** · 02-20-2013, 03:19 PM

Here we go! Thanks!

for olives 2.xlsx

**dilipandey** · 02-21-2013, 04:24 AM

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>

**olives** · 02-21-2013, 02:45 PM

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

**dilipandey** · 02-22-2013, 04:17 AM

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>

**olives** · 02-22-2013, 03:33 PM

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.xlsx for olives 2.xlsx

**olives** · 02-23-2013, 01:45 PM

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

**dilipandey** · 02-26-2013, 04:37 AM

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>

**olives** · 02-26-2013, 02:13 PM

Originally Posted by dilipandey

... see the attached file...

I just downloaded it and will check it out tonight! Thanks, Dilip! I'm in heaven!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions