Vlookup Errors on Mismatching Data

[Pc1x1]

Goal:

Sheet: Main

Enter a Value in a Cell (A1) . I.E. 123-AB-456
Cell (B1): =IF(A1<>0, $A$1,"")
Have a Vlookup that takes from "Prices" Sheet on Cell, that takes Text and Numbers (C1):

=IF(B1<>"",LOOKUP(Main!B1,'Prices'!E5:E5201,'Prices'!C5:C5201),FALSE)

Sheet: Prices
C5: Test of Words E5: Item Number

I just want Excel to find an exact item number, with text in between, and then display the text description next to it.

Right now, I believe because of improper formatting its not working. It works on some, on other values it does not work, and picks a close match, even with a False statement. I had alot of data, but simplified this example to try to make my life easer. I am either getting the wrong result or getting an N/A error.

I am also open if theres a more precise or appropriate way other than Vlookup, I am just familiar with Vlookup, so thats what I was using.

I can upload an example if my explanation is not enough, where could I upload the file to? Thanks.

The Vlookup is in a merged cell, if that changes anything.

 

[jasonb75]

You're using lookup, not Vlookup, there is a big difference, however, Vlookup can't do what you want.

try

=index('Prices'!E5:E5201,match(Main!B1,'Prices'!C5:C5201,0))

If you swap columns C and E in the Prices sheet then you could use

=VLOOKUP(Main!B1,'Prices'!C5:E5201,3,0)

 

[Pc1x1]

You're using lookup, not Vlookup, there is a big difference, however, Vlookup can't do what you want.

try

=index('Prices'!E5:E5201,match(Main!B1,'Prices'!C5:C5201,0))

If you swap columns C and E in the Prices sheet then you could use

=VLOOKUP(Main!B1,'Prices'!C5:E5201,3,0)

Your right it is just a look up, I tried doing the index, and its giving me NA error.

 

[jasonb75]

That means that whatever is in B1 doesn't exist it the list in Prices.

 

[Pc1x1]

But it does exist, so I am confused, because I copied and pasted the values from the price list itself.

Edit:

I have things in a little bit different order in the actual worksheet, I think I may have to upload it, so it makes more sense, because I tried editing a few things, and using Vlookup, gave me REF#, so doesn't seem like I am getting anywhere heh! Perhaps if you can see what I made, and possibly see an error that I made, we can figure out whats wrong.

 

[jasonb75]

My bad, had the ranges reversed, try

=index('Prices'!C5:C5201,match(Main!B1,'Prices'!E5:E5201,0))

 

[Pc1x1]

That seemed to work, but I have to redo my file, as I saved the example over it . But thanks, that seems to have done the trick, I am going to try it on another one of my forms, and I'll report back if it fixed the problem. Thanks.