Vlookup only returning values when true ?!?

[mx5jmcb]

Hi,

I'm new on this forum, as with many of you guys i assume, a frustrating problem got me to sign up.

Basically I'm trying to get vlookup to work and get information from another worksheet I have in the same excel file. (see shipping rates by supplier worksheet for vlookup formula in supplier column)

=VLOOKUP(B4,'Product Nomenclature'!B:Y,6,FALSE)

The vlookup function will only return values when it is = true, which is not what i want since I am looking for exact values, when using false, I get N/A back as an answer.

I've tried, trim and value functions as well as formatting related causes which hasn't helped me thus far.

I've included my excel sheet hoping that perhaps one of you will be able to help me!

Thanks

Selling-price-worksheet_2013-03-04(2).xlsx

[benishiryo]

hi , welcome to the forum. i'm guessing you need this:
=INDEX(Table2[SUPPLIER REFERENCE],MATCH(Table6[[#This Row],[SKU ]],Table2[PART NO'#],0))

VLOOKUP looks from the left column & returns the right. your lookup_value exist in column W while what you want is in column G, so that can't normally work with VLOOKUP. INDEX & MATCH should help you. and you dont have to refer to the whole column like B:B since you are using Tables.

the red column is what you want to return (i referred to G5:G237)
the green value is B4 in "Shipping rates by supplier"
the blue column is where you can find the Part # (i referred to W5:W237)

[mx5jmcb]

Thank you so much, having this work makes my day start off much better!

[mx5jmcb]

I got a little further and realize I have another problem

Basically, since Im using related data across different worksheets, when I end up sorting in the reference worksheet, the data contained in the other work sheet(s) does not correspond anymore to the referenced value.

How can I keep the integrity of my tables between worksheets when using sorting & other functions?!?

[ag273n]

when the data is already returned and you need not anymore to change the returned value, you can do copy and paste special: values - to maintain the returned value and remove the formula... that way, even if the source of the vlookup is changed, your returned value remains the same...

shortcut key
alt + hvv (paste special - values)(excel 2007)

[mx5jmcb]

Hi thanks for the suggestion, the thing is that I will be updating the file all the time so I will want the index formula to stay.

[emergy]

Try the following:

=INDEX(Table3,MATCH([@SKU],Table2[[#All],[PART NO'#]],0),MATCH("SUPPLIER REFERENCE",Table2[#Headers],0))

Table3 is a new Named Range referring to $B$4:$Y$238 on sheet "Product Nomenclature"
The remaining references are unchanged

Hope this works.

Cheers