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

A	B	C	D	E
Ref	Description	Cost	Cost inc VAT	Overall %
1	Example			1.5%

<tbody>
</tbody>

BACKUP PAGE - EXAMPLE

A	B	C	D	E	F
Ref	Description	Qty	Unit	Rate	TOTAL
1.1	AAA	12	M²	1	12
1.1	BBB	5	M³	5	25
	TOTAL CARRIED TO SUMMARY				37

<tbody>
</tbody>

 

[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