Need Help Deciphering this function

[TheBird]

Any help deciphering this would be greatly appreciated! I see an error function but the 2 vlookups are what i'm struggling with.

=IF(ISERROR(VLOOKUP($H12,dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,dusa,4,FALSE))

 

[hipnotode]

looks tome like it looks up the value from an exact match from the cell $H12 from dusa, and returns from a 4th column. but if that value is an error, it returns a 0 instead.

 

[T. Valko]

Any help deciphering this would be greatly appreciated! I see an error function but the 2 vlookups are what i'm struggling with.

=IF(ISERROR(VLOOKUP($H12,dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,dusa,4,FALSE))

VLOOKUP($H12,dusa,4,FALSE)

Let's assume H12 = X

It's looking for "X" in the first column of the named range "dusa". And, if found, returns the value from the 4th column of the named range on the same row that contains "X". FALSE means to look for an exact match of "X" (case is not a factor. X and x would be "exact" matches).

Let's assume "dusa" is the named range that refers to Sheet1 C12:F15.

H12 the lookup value = X

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">A</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">51</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">60</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">79</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">X</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">66</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">X</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">35</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">66</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Z</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">42</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">31</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Y</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">27</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">71</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">54</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR></TBODY></TABLE>


The result of the formula is 66.

The formula can be reduced to:

=IF(ISERROR(VLOOKUP($H12,dusa,4,0)),0,VLOOKUP($H12,dusa,4,0))

0 means the same as FALSE.

 

[Matty]

looks tome like it looks up the value from an exact match from the cell $H12 from dusa, and returns from a 4th column. but if that value is an error, it returns a 0 instead.

I agree. Though the =TRUE bit is superfluous.

Matty

 

[Akashwani]

Hi, Welcome to MrExcel

Beaten to it, but here's my interpretation anyway.

=IF(ISERROR(VLOOKUP($H12,dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,dusa,4,FALSE))

=IF(Logical Test, Value if true, Value if False)

ISERROR Checks to see if the result of the Vlookup is an error or not.
If it is an error put 0 in the cell, if it isn't an error put the result of the Vlookup in the cell.

Vlookup is looking at the data in H12 and seeing if it is the Named Rang dusa (Sheet1 A2:D7), if it is, return the data from the 4th column along.

Excel Workbook
	A	B	C	D
1	Number	Animal	Colour	Name
2	123	Cat	Red	Bill
3	124	Dog	Green	Bob
4	125	Cow	Blue	Tom
5	126	cat	Red	Tim
6	127	dog	Green	Jim
7	128	Pig	Blue	Jo
Sheet1

Excel Workbook
	H	I
12	123	Bill
13	999	0
Sheet2

I hope this explains it for you.

Ak

 

[T. Valko]

Hi, Welcome to MrExcel

Beaten to it, but here's my interpretation anyway.

=IF(ISERROR(VLOOKUP($H12,dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,dusa,4,FALSE))

=IF(Logical Test, Value if true, Value if False)

ISERROR Checks to see if the result of the Vlookup is an error or not.
If it is an error put 0 in the cell, if it isn't an error put the result of the Vlookup in the cell.

Vlookup is looking at the data in H12 and seeing if it is the Named Rang dusa (Sheet1 A2:D7), if it is, return the data from the 4th column along.

Excel Workbook
	A	B	C	D
1	Number	Animal	Colour	Name
2	123	Cat	Red	Bill
3	124	Dog	Green	Bob
4	125	Cow	Blue	Tom
5	126	cat	Red	Tim
6	127	dog	Green	Jim
7	128	Pig	Blue	Jo
Sheet1

Excel Workbook
	H	I
12	123	Bill
13	999	0
Sheet2

I hope this explains it for you.

Ak

Am I the only one that noticed the syntax error in the original formula?

 

[Akashwani]

Hi Biff,

I guess so, point it out to me please.

=TRUE????

Ak

 

[TheBird]

Thanks for all the help! Our Excel guy is out sick and we're muddling through!

 

[Matty]

Am I the only one that noticed the syntax error in the original formula? :wink:

=IF(ISERROR(VLOOKUP($H12,Dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,Dusa,4,FALSE))

The ) should be after FALSE), closing ISERROR(), but as I mentioned previously, it's simply not needed.

Matty

 

[Aladin Akyurek]

Any help deciphering this would be greatly appreciated! I see an error function but the 2 vlookups are what i'm struggling with.

=IF(ISERROR(VLOOKUP($H12,dusa,4,FALSE)=TRUE),0,VLOOKUP($H12,dusa,4,FALSE))

If VLOOKUP is supposed to return a number...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP($H12,dusa,4,0)))

would probably be a tad faster.