Expected Value not returned in Vlookup Statement combined with IF

MrPink1986

Active Member
Joined
May 1, 2012
Messages
252
HI,

My formula below is not pulling back the expected value. I am running a vlookup to look first at the Vendor tab in my s/s. If this is Blank "", or an error it should look to the pRC tab and pull back the value if not here the comment "NO Price" is returned.

When running this formula I am getting back "NO Price" even though there is a vlaue in the PRC tab.

Any help greatly appreciated.


=IFERROR(IF(A14="","",IF(OR(VLOOKUP(E14,'Vendor'!$A:$AR,22,0)="",ISERROR(VLOOKUP(E14,'Vendor'!$A:$AR,22,0))),
VLOOKUP(A14,'PRC'!A:W,23,0),VLOOKUP(E14,'Vendor'!$A:$AR,22,0))),"No Price")
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I think the No Price is because of the IFERROR, i.e there is an error the first IF is TRUE
 
Upvote 0
Something like...
Rich (BB code):
=IF(A14="",
  "",
  IF(ISNUMBER(1/VLOOKUP(E14,'Vendor'!$A:$AR,22,0)),
   VLOOKUP(E14,'Vendor'!$A:$AR,22,0),
   IF(ISNUMBER(1/VLOOKUP(A14,'PRC'!A:W,23,0)),
    VLOOKUP(A14,'PRC'!A:W,23,0),
    "No Price")))

Does this satisfy?
 
Upvote 0
Thanks - it worked perfectly. .


Something like...
Rich (BB code):
=IF(A14="",
  "",
  IF(ISNUMBER(1/VLOOKUP(E14,'Vendor'!$A:$AR,22,0)),
   VLOOKUP(E14,'Vendor'!$A:$AR,22,0),
   IF(ISNUMBER(1/VLOOKUP(A14,'PRC'!A:W,23,0)),
    VLOOKUP(A14,'PRC'!A:W,23,0),
    "No Price")))

Does this satisfy?
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top