# Vlookup with If formulas issues,

#### marcdarne

##### New Member
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>

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this:

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

Or this to make errors 0:

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

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

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)

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.

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

Replies
17
Views
926
Replies
1
Views
897
Replies
2
Views
405
Replies
0
Views
425
Replies
3
Views
980

1,202,964
Messages
6,052,829
Members
444,602
Latest member
Cookaa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back