• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

error in my VLookup

A

Aspur

New Member
I have formulated a Vlookup that looks for cost in two different excel tabs. The first step is to look in the "item cost" tab and if the cost is not found there to go look in the "cost pull from backend" tab. If cost is not found on either tab then "No Cost" should appear in the cell.

My Vlookup is like below, but I'm finding that it doesn't seem to go to the Cost pull tab to get the data .. I found it says No Cost when there is a cost in the cell. What have I done missed in the formula? FYI, this is my first attempt at something like this. Appreciate the input.

=IFERROR(VLOOKUP(LEFT(C:C,20),'Item Cost'!A:B,2,0),IFERROR(VLOOKUP(LEFT(C:C,20),'Cost pull from backend'!A:E,5,0),"No Cost"))
 
You reference a full column as lookup value. Perhaps change LEFT(C:C by LEFT(C2; assuming row 2 is where your data starts.
 
Thank you for the reply. I was able to remove the reference to the item cost tab (it's not needed any longer)

The problem is different. When I looked closed at the data, the problem seems to be two fold :
1) Lookup value is text
2) array is general/ number

The array has leading zero's for SOME part numbers, but not all. If I place a "'0" in front of a part number with an error, the cost appears in the cell. I don't know how to make up for this variance in the data. Unfortunately, the text data in Item Cost comes from somewhere else and the data in the Query pull from the backend is a query table that pulls directly form the DB it's looking against. I"m not sure there is a solution for this.
 
Hi,

Can you post a sample version ?


Blind shot would be to force the lookup value and array into numbers and use this array formula:

=IFERROR(VLOOKUP(LEFT(C2,20)+0,('Item Cost'!$A$2:$B$9999)+0,2,0),IFERROR(VLOOKUP(LEFT(C2,20)+0,('Cost pull from backend'!$A$2:$E$9999)+0,5,0),"No Cost"))

{array formula needs to be entered with Ctrl+Shift+Enter}

Regards,
 
Thanks for the response Khalid .. I got it figured out so I'll post it for others to lean from;
As it turns out, I didn't need to the 'Item Cost' tab after discussion with the management - that made things a little easier. I address the leading zeros in the formula below

=IFERROR(VLOOKUP(LEFT(C167,20),'Cost pull from backend'!A:E,5,0),IFERROR(VLOOKUP("0"&[@[Item ]],'Cost pull from backend'!A:E,5,0),"No Cost"))
 
You must log in or register to reply here.
Back
Top