How to write a formula with IF and vlookup

November 8, 2010 at 19:26:37
Specs: Windows Vista
I know how to explain the formula I need but I can't write it. If someone understand what im writing and can turn it into a formula it would be greatly appreciated.

IF cell B is the minimum of the whole range Then take cell A to look up this page anre return the page name. IF cell B is the maximum of the whole range return page name. Else leave it blank.

As far as im concerned there should be 2 IF's and 1 Vlookup. Thanks


See More: How to write a formula with IF and vlookup

Report •

#1
November 8, 2010 at 20:24:08
We'll need more specific info.
Right your IF formula looks something like this:

=IF(MAX(somerange)=some_unknown_number_in_cellB1,
     vlookup(somewhere,using_perhaps_CellA1),
          IF(MIN(somerange)=some_other_unkown_number_in_cellB1,
                vlookup(somewhere,using_perhaps_CellA1),""))

MIKE

http://www.skeptic.com/


Report •

#2
November 8, 2010 at 20:27:40
Im supposed to use the vlookup from a different tab in the excel sheet. The range is in the whole column like the minimum of the whole column. I really don't know how else to put it. Let me know what else I can do to help you help me.
If B(min of all B column then take A to look up this page and return it here), If B(max of all the B column then take A to look up this page and return it here), Else leave it blank.

This should have 1 vlookup and 2 if's


Report •

#3
November 8, 2010 at 21:16:02
Post a small sample of your data and what it should look like before and after.

See this How To before you post, it will explain how to use the pre tags:

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 8, 2010 at 22:02:29
I tried putting it on here but it does not set up correctly not everything is how it is supposed to be and if its out of order its impossible to understand. Can I send an email with the information instead?

Report •

#5
November 9, 2010 at 06:01:31
Can I send an email with the information instead?
The object of the forums is so that we can all learn and see how the various problems are solved, sending me a private email kinda defeats the purpose.

You keep saying: This should have 1 vlookup and 2 if's
Is that a requirement?
Is this a school assignment?

Post a small sample of your data
We do not need your whole sheet,
a half dozen lines of data with the appropriate column and line headings
and how it is set up should be sufficient.

What have you tried so far?
Post the formulas that you have tried.

MIKE

http://www.skeptic.com/


Report •

#6
November 9, 2010 at 07:21:04
It is not a requirement I was just told it should have 2 if's 1 vlookup. Yes it is for school it is what we are going to have for a test. These are practice problems. I've tried some formulas but I beleive they're way off and it keeps on giving me a message. I tried putting about 7 lines of the problem but it does not keep it in a row. Ill try it again. I tried again it does not come up normal. If I post it without pretags I think it will come up normal. Should I do that and seperate the numbers with like a dash or slash?

Report •

#7
November 9, 2010 at 08:05:26
Just post what you can, and include your formulas.

MIKE

http://www.skeptic.com/


Report •

#8
November 9, 2010 at 08:39:23
Cell A/ Cell B/ Cell C/ Cell D/ Cell E
Page ID/Page Views/Percentage/Page Name/Max/Min
28000546/ 1457170/ 49.49%/Home Page/Max
28000550/ 74880/ 2.54%/
28000911/ 11190/ 0.38%/
28000961/ 8450/ 0.29%/
28001043/ 13450/ 0.46%/
28001143/ 4010/ 0.14%/Kids 2/Min

So the I need to match the page ID with another tab in excel to ge the page name to appear on this page. Then I need to get the max or min of the percentage column to say max or min or so it leaves it blank. I couldn't get far because I don't know how to use the vlookup. I did =if(max(C:C),(min(C:C) then I dont know what to do. I hope this helps.


Report •

#9
November 9, 2010 at 09:04:17
Cell A/ Cell B/ Cell C/ Cell D/ Cell E

In Excel, common usages is Column letters and Row numbers

A Cell is the intersection of a Column and Row, IE A1 or Y25 or DZ12

It may sound pickey, but learn the basics now, because everything that follows builds upon your knowledge of them.

So the I need to match the page ID with another tab in excel to ge the page name to appear on this page.

This is where you will need a =VLOOKUP() using your Page ID

Then I need to get the max or min of the percentage column to say max or min or so it leaves it blank.

This is where you will need to know about, =IF() functions, =MAX() functions and =MIN() functions.

MIKE

http://www.skeptic.com/


Report •

#10
November 9, 2010 at 11:17:00
I know the column and rows well. I just don't know how to use the extreme formulas like the vlookup and nested if's. I mean to me this is extereme to others it might not be. Anyway thank you for the help.

Report •

#11
November 9, 2010 at 11:45:01
The structure of an =IF() statement is:

=IF(Logical Test , Value If True , Value If False)

The three sections are separated by a comma.

The First section is some type of Logical Test, IE

A1 = A2 is a logical test, it is asking:
is the value in cell A1 equal to the value in cell A2,
it will return either True or False depending on the values in cell A1 & cell A2

A1 <> X5 is a logical test, it is asking
is the value in cell A1 not equal to the value in cell X5
it will return either True or False depending on the values in cell A1 & cell X5

MAX(C1:C100) = A1 is a logical test, What does the =MAX() function do?
MIN(C1:C100) <> B2 is a logical test. What does the =MIN() function do?


The second and third sections ask:
what to to IF the Value is True,
or
what to do IF the Value is False

If the Logical Test returns TRUE, then write the word YES
If the Logical Test Return FALSE, then write the word NO

=IF(A1 = A2,"YES","NO")
=IF(Logical Test , Value If True , Value If False)

=if(A:Amax,A:Amin,"")

What is wrong with the formula you posted?

MIKE

http://www.skeptic.com/


Report •

#12
November 9, 2010 at 11:47:28
=if(A:Amax,A:Amin,"") it says NAME


Report •

#13
November 9, 2010 at 11:51:32
Bud everything you mention above I know. What I don't know is how to set it up so I get the proper answer I know all the =IF(Logical Test , Value If True , Value If False). How do I ask it to put min or max? How do I ask it to leave it blank?

Report •

#14
November 9, 2010 at 12:13:37
it produces the ERROR message #NAME?

Because your formula:

=if(A:Amax,A:Amin,"")

is WRONG on a very basic level,
it does not have a Logical Test,
it doesn't even have a valid Value If True statement.


Have you taken the time to looked up what the =MAX() and =MIN() functions DO?
Have you taken the time to test them and see what results you get?
They can be used as a Logical Test as I showed in post #11

MIKE

http://www.skeptic.com/


Report •

#15
November 9, 2010 at 12:17:53
Yes Max and Min give you the Maximum or Minimum of a column

Report •

#16
November 9, 2010 at 12:38:50
Yes Max and Min give you the Maximum or Minimum of a column

Correct,
so if you compare the output of =MAX() or =MIN() to the value in a particular cell and you have a Logical Test.

MAX(C1:C100) = A1 is a logical test,
MIN(C1:C100) <> B2 is a logical test.

Using either =MAX() or =MIN() gives you the first part of your =IF() statement

Using your data:

     A            B      C         D        E
1) 28000546   1457170 49.49%   Home Page   <Formula>
2) 28000550   74880    2.54%		
3) 28000911   11190    0.38%		
4) 28000961   8450     0.29%		
5) 28001043   13450    0.46%		
6) 28001143   4010     0.14%   Kids 2      

In cell E1 you need a formula that begins like:

=IF(MAX($C$1:$C$6)=C1,.........

What do you display in the Value If True section?

What do think you do for the Value If False section?

Why do I use the symbol $ in the =MAX() function?

MIKE

http://www.skeptic.com/


Report •

#17
November 9, 2010 at 12:46:20
The symbol $ is used so the cells don't continue it freezes it I know that. If true it should say max. Same goes for the min if true it should say min. If false it should leave blank. But also I need to do a formula for D to vlookup and bring the name over to D from the other excel tab

Report •

#18
November 9, 2010 at 12:50:29
If true it should bring the name from the other excel tab if false it should leave it blank.

That's column D, the =VLOOKUP section, where not that far along yet.

Where only doing column E:
Then I need to get the max or min of the percentage column to say max or min or so it leaves it blank.

MIKE

http://www.skeptic.com/


Report •

#19
November 9, 2010 at 12:54:53
I don't know what to write for it to say max or min or leave it blank

Report •

#20
November 9, 2010 at 13:03:39
I don't know what to write for it to say max or min or leave it blank

That's what were doing.....

Using your data:

     A            B      C         D        E
1) 28000546   1457170 49.49%   Home Page   <Formula>
2) 28000550   74880    2.54%		
3) 28000911   11190    0.38%		
4) 28000961   8450     0.29%		
5) 28001043   13450    0.46%		
6) 28001143   4010     0.14%   Kids 2      

In cell E1 you need a formula that begins like:

=IF(MAX($C$1:$C$6)=C1,.........

What do you display in the Value If True section?

What do you think you do for the Value If False section?

Why do I use the symbol $ in the =MAX() function?

MIKE

http://www.skeptic.com/


Report •

#21
November 9, 2010 at 13:04:43
If true write max if false leave blank

Report •

#22
November 9, 2010 at 13:08:41
If true write max if false leave blank

Your half right,
What about showing the MIN value?

our formula now looks like this:

=IF(MAX($C$1:$C$6)=C1,"Max",.........

What do we need to do to test for a Min value?

MIKE

http://www.skeptic.com/


Report •

#23
November 9, 2010 at 13:10:32
=IF(MIN($C$1:$C$6)=C1,"MIN",

Report •

#24
November 9, 2010 at 13:14:13
=IF(MIN($C$1:$C$6)=C1,"MIN",

This will check for a MIN value and display the text string MIN,
but what about the MAX value and
what about the Value If False section of the =IF() statement?

MIKE

http://www.skeptic.com/


Report •

#25
November 9, 2010 at 13:19:24
i thought we already did max

Report •

#26
November 9, 2010 at 13:27:35
i thought we already did max

We did it partially, but you can have only one formula in a cell.
So if you need to show BOTH Max and Min what do we need to do?

Remember the structure of an =IF() statement is:

=IF(Logical Test , Value If True , Value If False)

The three sections are separated by a comma.

We've worked out:

=IF(MAX($C$1:$C$6)=C1,"Max",

and

=IF(MIN($C$1:$C$6)=C1,"MIN",

but both are not complete formulas.

Both formula are missing the Value If False section.

And since you can have only one formula in a cell, what do we do?

MIKE

http://www.skeptic.com/


Report •

#27
November 9, 2010 at 13:31:54
if false we put ""

Report •

#28
November 9, 2010 at 13:35:16
if false we put ""

Still does not answer the question of how do we do TWO tests (one for MAX and one for MIN) in a single formula.

MIKE

http://www.skeptic.com/


Report •

#29
November 9, 2010 at 13:44:42
I dont know

Report •

#30
November 9, 2010 at 14:42:26
You need to combine both tests in one formula.

So if we have the test for MAX and MIN partially worked out,with either one needing a Value If False section:

=IF(MAX($C$1:$C$6)=C1,"Max",

and

=IF(MIN($C$1:$C$6)=C1,"MIN",

How would you combine the two statments into one?
An =IF() statment can be used in a Value section.
and we need a Value If False section.

Somthing along the lines of this should work:

=IF(MAX($C$1:$C$6)=C1,"Max",IF(MIN($C$1:$C$6)=C1,"MIN",""))

MIKE

http://www.skeptic.com/


Report •

#31
November 9, 2010 at 14:49:45
That works. Thank you

Report •

#32
November 9, 2010 at 15:04:19
Did you put the formula in cell E1 and drag down so that the formula is in each cell from E1 thru E6?

MIKE

http://www.skeptic.com/


Report •

#33
November 9, 2010 at 15:08:59
Yes I did. I dont know how to do the Vlookup though but I have to leave in 5 mins to go to class. Thank you very much I at least got the first part

Report •

#34
November 9, 2010 at 17:47:31
I dont know how to do the Vlookup

What have you tried?

MIKE

http://www.skeptic.com/


Report •

#35
November 10, 2010 at 20:24:30
The teacher explained it in class. Thank you

Report •

#36
June 7, 2011 at 17:38:32
I have been racking my brain trying to incorporate an if statement with two vlookups, to provide a Yes or No. What I am trying to do is, return a Yes in the Worthy column if either the Material is found on a separate spreadsheet or the program combination is found in a separate spread sheet, return a Yes if found, otherwise a No. I do not want a #n/a, as I use the yes/no for other calculations.
I have tried if statements, or statements, if(or statements, but cannot get them to work.

The first spreadsheet is the working sheet, the 2nd spreadsheet is the tab i am looking up the material on, and the third spreadsheet is the program data. Need help

Sold To Material Program Worthy ?
1000001422 3FE23086AA 1000001422/3FE23086AA #N/A
1000001422 108994989 1000001422/108994989 Yes
111111 408837490 111111/408837490 Yes


Part Number Status
3FE23086AA Yes
3FE25676BA Yes


Program Status
1000000894/109199067 Yes
1000000894/109313197 Yes


Thanks
Steve


Report •

#37
June 8, 2011 at 03:37:39
Spro78, please ask your question in a new thread.

Also read this How-To to learn how to use the PRE tags so all your data lines up neatly when you post.

http://www.computing.net/howtos/sho...

As for your #N/A problem, try looking up, in the Excel Help files: =ISNA()

MIKE

http://www.skeptic.com/


Report •

Ask Question