Vlookup with If formulas issues,

[marcdarne]

Hi All,

Having problems trying to create the right formula for what I'm after. I have tired to do a Vlookup with a If formula however it returned an error.

Basically what I am attempting to do is have a cell in one worksheet find a string of text in another worksheet called "CARRIED TO SUMMARY" then return the figure in the cell adjacent. I also wanted it to return a zero figure if there was no figure in that cell rather than a N/A# message.
Sample below

SUMMARY PAGE

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Ref[/TD]
[TD]Description[/TD]
[TD]Cost[/TD]
[TD]Cost inc VAT[/TD]
[TD]Overall %[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Example[/TD]
[TD][/TD]
[TD][/TD]
[TD]1.5%[/TD]
[/TR]
</tbody>[/TABLE]

BACKUP PAGE - EXAMPLE

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Ref[/TD]
[TD]Description[/TD]
[TD]Qty[/TD]
[TD]Unit[/TD]
[TD]Rate[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]AAA[/TD]
[TD]12[/TD]
[TD]M²[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]BBB[/TD]
[TD]5[/TD]
[TD]M³[/TD]
[TD]5[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL CARRIED TO SUMMARY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Try this:

=VLOOKUP("TOTAL CARRIED TO SUMMARY",'BACKUP PAGE'!$B$1:$F$1000,5,0)

 

[steve the fish]

Or this to make errors 0:

=IFERROR(VLOOKUP("TOTAL CARRIED TO SUMMARY",'BACKUP PAGE'!$B$1:$F$1000,5,0),0)

 

[jim may]

to accommodate for your added "wanted it to return a zero figure if there was no figure in that cell rather than a N/A# message"

Modify the above to:

=IFERROR(VLOOKUP("TOTAL CARRIED TO SUMMARY",'BACKUP PAGE'!$B$1:$F$1000,5,0),0)

HTH,

Jim

 

[marcdarne]

Thanks Steve,

However still not working, received the following error:

[We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it, or click Help for more info on common Formula problems]

=IFERROR(VLOOKUP("TOTAL CARRIED TO SUMMARY",'Toxic Hazardous Material'!B$1$:$F1000$,6,0),0)

 

[steve the fish]

Hi

Your dollar signs are in the wrong place. They should go around just the letter of the cell reference. Not $F1000$ but $F$1000. Also you are out of range. B:F is 5 columns wide so you cant have ,6,0. Max is ,5,0.

 

[marcdarne]

Hi

Your dollar signs are in the wrong place. They should go around just the letter of the cell reference. Not $F1000$ but $F$1000. Also you are out of range. B:F is 5 columns wide so you cant have ,6,0. Max is ,5,0.

Thank you works a charm