Test

[Cat3TRD]

Is there a way to use VLOOKUP and have it ignore the first digit in the column it's searching?

I'm searching for a 10 digit number in an 11 digit column, and the first number is usually not important.

Even better, would be an IF option that would say, if it's over 10 digits, ignore the first digit.

 

[tweedle]

Something like this: =VLOOKUP(B3,MID(C1:C10,2,10),1,FALSE)
{commit the formula with CTRL+SHIFT+ENTER}


B3 needs to be text

 

[Cat3TRD]

Something like this: =VLOOKUP(B3,MID(C1:C10,2,10),1,FALSE)
{commit the formula with CTRL+SHIFT+ENTER}


B3 needs to be text

I'm not sure where I'm messing up in the formatting of this when trying to make it fit into my situation ...

Here's a rundown of what I'm working on with more specific detail..

There are two sheets, the first one called "Sheet1", the second one called "price file"

What I'm doing is scanning a product barcode, which enters a 10 digit number into Excel. I have them starting at A1 on Sheet1. I believe I have to have that column set up as text to keep the zero at the beginning of the number from being deleted.

I want B1 on Sheet1 to have a formula that searches the last ten digits of column A, starting at A2 in the price file sheet, and returns whats in column C of price file.

There are 50,353 rows in price file.

The number of digits in column A of price file is not always 11 digits. There a small number that have nothing, there are maybe 100 rows that have 3-9 digits, then there's another maybe 500 or so rows that have 10 digits, but the vast majority of the 50,000 rows have 11 digits.

I tried to make your formula fit into my workbook, but I must be doing something wrong. It's giving me "#VALUE!"

When setting "price file" as the table array, do I need to use the F4 command that changes the : to a $?

I've always done it that way, because that's how I was shown how to do it, but I really don't know what purpose that serves.

Thank you for the really fast reply

 

[gsistek]

try using:

in B2 =IF(LEN(A2)<11,INDEX('price file'!A:C,MATCH(A2,'price file'!A:A,0),3),INDEX('price file'!A:C,MATCH(RIGHT(A2,10),'price file'!A:A,0),3)

then drag down.

 

[tweedle]

Excel Workbook
	A	B
1	0025687922	500.2
Sheet1

Excel Workbook
	A	B	C
18	1-0025687922		500.20
price file

 

[Cat3TRD]

Ok, here's an update to where I'm at now.

I separated out all of the 11 digit rows in my price file. I ran a function to delete the first digit of those. So now I have everything with a 10 digit number that needs to have 10 digits. (don't know why I didn't do this sooner... my apologies)

But now I've hit another snag.

There's three numerical columns of data to reference in my price file.
There's a UPC column, an item number column (these item numbers come from our distributor) and a NDC column.

The UPC column is what I have to have VLOOKUP search through.
The NDC column is the preferred result to get back.
There are some over the counter medications that aren't required to have an NDC.

Is there a way to have VLOOKUP try the NDC column, and if it's blank, return the item number?

Thank you all very much for your help. Excel is really an awesome program. I've only recently (maybe 6-12 months) started using it. I keep finding more and more uses for it, and I can usually get over hurdles by googling my issue, but sometimes I just can't find a solution that works for me. Thank you again for working with me on this project

 

[tweedle]

Possibly a posted sample of data would help us help you.

 

[Dryver14]

hope this works for you

Excel Workbook
	A	B	C	D	E	F	G	H
1	3456789123					UPC	Item Number	NDC
2						1234567891	324	3333
3	145					2345678912	234	4444
4						3456789123	145
5						4567891234	137	6666
Sheet1

you will need to reference your sheets and table

 

[Cat3TRD]

Well, I don't have that Excel Jeanie, but I'll see what I can do here.

Sheet1

A B
0123456789 (Formula goes here)

For these, column A is the 10 digit UPC that gets entered when I physically scan a barcode. Column B is a VLOOKUP formula that I want to check against the next sheet "price list."


price list

A B C
0123456789 012345 01234567890
9876543210 543210


For these, column A is the 10 digit UPC that the VLOOKUP is searching for. Column B is an item number from the distributor we purchase these products from. Only the items that we can get from that distributor have an item number. Column C is the number I really need, but some items don't have that number. For the ones that don't have a number in column C, I can use the number in column B. There will be VERY few that don't have either, and I can work with those on a case by case basis.

So, what I'm trying to do is have VLOOKUP find a number in column A on price list, and return the number from column C if there's something there to return, if not, then return the number in column B.

If there's a VLOOKUP if/then scenario that will put the number from B when there's nothing in C, that would save a lot of time.

Thanks again

EDIT:
My spacing disappeared after submitting. I'll try again at home, and upload a screen shot to my webhost to illustrate my intentions.

 

[Dryver14]

Excel Workbook
	A	B
1	1234567891	12345
Sheet1

I have only gone down 100 rows on the price list sheet you will need to adjust it to haw many rows you have