=if and a =vlookup combined

[flyingmonkeyofdoom]

Firstly i was wondering if this was possible if it was I was wondering if someone can help me combine a =if and =vlookup statement

I can work out the vlookup; but i'm not seeing the =if part

What i require is for the =if to read a unit number, name and date and if they are there to do the =vlookup
But this would have to match a previous set of numbers


Example

Unit number Name Date
1 James 1/1/10

Would need to match

Unit number Name Date
1 James 1/1/10

If they didnt then do nothing if they do do the vlookup

Any help would be greatly appriciated

 

[Rhino373]

I would use the count() function. In a count, the cell is not added if it is blank. This means if the count doesn't equal 3 (since you are looking at 3 different criteria) then we don't want to run the vlookup. If it does then we want to.

Here is an example:

=IF(COUNT(F19:H19)=3,VLOOKUP(F19,$K$19:$L$22,2),"no vlookup")

You replace your range with what I have in red, your vlookup with what I have in green and what you want to happen if there is a blank field with what I have in blue.

 

[flyingmonkeyofdoom]

thank you i've reworked the equation to:

=IF(COUNT($A1:$C1),IF(COUNT($D1:G1),VLOOKUP(R7:R7,K$7:L$17,2,0)),"")

the only problem is that as long as there are three on each side it matches and does the equation

i need to so that if its different to not do it

example:

1 james 1/1/10 1 james 1/1/10 (run vlookup)
1 james 1/1/10 1 james 2/2/10 (no run equation)

or isnt this possible?

 

[Rhino373]

I'd change to the SUM() function then

=IF(SUM(A1:C1)=SUM(D1:F1),VLOOKUP(R7:R7,K$7:L$17,2,0)),"")

The only potential drawback is that "james" = "JAMES"

 

[Rhino373]

Hmm, nevermind, it isn't even taking the value of james into account.

 

[flyingmonkeyofdoom]

The names will be entered in the same format everytime so that shouldnt be a problem

The only thing it doesnt like is the ,"") at the end as it says that it contains an error

if i remove it it works (but have a massive FALSE message appear)

 

[Rhino373]

=IF(B1=E1,IF(SUM(A1,C1) = SUM(D1,F1),VLOOKUP(R7:R7,K$7:L$17,2,0),""),"")

This is where B1 and E1 are the text names "james" and the other cells are numeric or a date.

 

[Rhino373]

The names will be entered in the same format everytime so that shouldnt be a problem

The only thing it doesnt like is the ,"") at the end as it says that it contains an error

if i remove it it works (but have a massive FALSE message appear)

There was an extra close bracket from the Vlookup. If you want to use that line and believe the names are okay the code looks like this:

=IF(SUM(A1:C1)=SUM(D1:F1),VLOOKUP(R7:R7,K$7:L$17,2,0),"")

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>K</th><th>L</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Unit</td><td style="text-align: center;;">Name</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Unit</td><td style="text-align: center;;">Name</td><td style="text-align: center;;">Date</td><td style="text-align: center;background-color: #FFFF00;;">Result</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">james</td><td style="text-align: center;;">1/1/2010</td><td style="text-align: center;;">1</td><td style="text-align: center;;">james</td><td style="text-align: center;;">1/1/2010</td><td style="text-align: center;background-color: #FFFF00;;">H</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">james</td><td style="text-align: center;;">1/1/2010</td><td style="text-align: center;;">1</td><td style="text-align: center;;">james</td><td style="text-align: center;;">2/2/2010</td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">A</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">B</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;">C</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">9</td><td style="text-align: center;;">D</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">10</td><td style="text-align: center;;">E</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">F</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;">G</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;">H</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">I</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">J</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;">K</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">$A2:$C2=$D2:$F2</font>)</font>)=3,VLOOKUP(<font color="Red">R7,K$7:L$17,2,0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[flyingmonkeyofdoom]

thank you

this

=IF($B1=$E1,IF(SUM($A1,$C1) = SUM($D1,$F1),VLOOKUP(Q7,K$6:L$16,2,0),""),"")

works great and #n/a can be edited out

i'll have to make sure that i manually edit out the #N/A and making sure theres data, any data, in an empty cell