Vlookup across multiple values to return only oneClosed

Question

Bit of a long one to explain, but im sure there is a quick answer.

I have a barcode scanner which is used to input data. This data goes into column B. There is another set of data which is scanned into column C. 

The codes that are scanned need certain characters extracted from them depending on what the product it is to get the 'official' part code. This can be one of three possibilities so I have used column D,E & F to extract the relevant data using the formulas LEFT, MID & CONCATENATE, taken from the information in-putted into column B & C.

So D1, E1 & F1 are populated with various strings of text, but out of these three, only E1 is the one I need.The same is repeated in D2, E2 & F2 but this time, the text in F2 is the one I need. 

This will continue down in a random order for 200+ rows. 

So what I need help with is to create a lookup (or other function) to look at what is in D1, E1 & F1 and compare that with my official part code list and return a value within the full part code. 

Lets say my official part code list is on sheet 2 and populates A1:D1000. From the lookup if a match is found, I need to display column 2,3,4 (which lists the part description, quantity and status of the part)

My previous way of doing it was having three separate lookups checking the value in D1. Another lookup for E1 and then again for F1. Out of the three lookup results, one would produce the correct parts and the other two were giving an #N/A. I then used the ISNA function  to check a return the value with the correct text. This I know is a very long winded way of doing it and makes it difficult if a 4th variation of a part code is needed. 

So this is where I ask for help! As usual im sure there are many ways of doing it but I can only do it the long way! The ultimate goal is for me to give my contractors a scanner and a locked excel sheet. they scan a barcode and the sheet tells them if the part is to be kept or scrapped! 

If it helps at all, ive uploaded a sample of data to my dropbox (link below). The cells in yellow are my scanned input and the cells highlighted in red are the correct part codes which will appear in the lookup data table. 

https://www.dropbox.com/s/d8i6and0sig96jb/Part%20code%20lookup.xlsx

venkat1926 · Answer

I am confused. the problem is with translating thoughts into text. It happened to me also sometimes. 
D1 E1 F1 are not data but merged cells and headings
D2 E2 F2 are also not data but invdiviual column headings.
Perhpas you are thinking from row no. 3
your selection is E3  D4 and F5

ok these data are available in the second table. They are red colored 
 then what?
you mean corresponding to the red selection in main table, do you want vlookp of (is it third column or fourth column of second table. perhaps it is possible to refer to red marked data in the first sheet for each row as reference cell for vlookup and ref table being second table

for e.g. for first red  nk69772.correspoding scanned numbers are  are in B3 and C3. then what??

some more clarifications will be helpful.

venkat1926 · Answer

I am not using vlookup
I use a macro and find function. run the macro and see column G

still I am not clear why is coloumn K

Sub test()    Dim j As Integer, k As Integer    Dim r As Range, r2 As Range, cfind As Range    Dim x As String, m As Integer, y As String    j = Range("A3").End(xlDown).Row    Range(Range("G2"), Cells(j, "G")).Cells.Delete    Set r = Range(Range("a2"), Range("a2").End(xlToRight).End(xlDown))    'msgbox r.Address    Set r2 = Range(Range("H11"), Range("H11").End(xlToRight).End(xlDown))    'msgbox r2.Address    For k = 3 To j        For m = 4 To 6            x = Cells(k, m)            x = StrConv(x, vbUpperCase)            'msgbox x            Set cfind = r2.Find(what:=x, lookat:=xlWhole)            If Not cfind Is Nothing Then                'msgbox cfind.Address                y = Cells(cfind.Row, "J")                'msgbox y                GoTo nextstep            Else: GoTo nextm            End Ifnextstep:            Cells(k, "G") = y            GoTo nextknextm:        Next mnextk:    Next k    MsgBox "macro over"End Sub

SpaceMonkees · Answer

That works great! 

However I dont think it is going to be ideal for the main project due to my lack of up to date VB knowledge and the need for simplicity for the contractors. It works, but I would be limited in amending it. 

how would I extend this to cover more rows covering columns B-F? IE the part code list could be 200 lines long so i need to ensure the macro runs for all part codes in column B&C. 

And what would happen if another part code variation appears? Can I insert a new column in between F & G?

And tradtiionally, my lookup data is on another workbook. What code would be needed here? 

I really do appreciate what you have done and you have provided an answer to exactly what was needed... I just need to expand on it and change it from the test document into the main sheet. 

I will have a play about with it and change some of the code and see if it works. Its been 15 years since I last used VB!! :D

venkat1926 · Answer

I have used end(xltoright) and end(xldown). I suppose it will work even if more rows and columns to be added. anyhow it has to be checked and macro debugged and modified if necessary.  test it on your extended data (KEEP THE DATA FILE SAFELY SOMEWHERE) AND GIVE FEEDBACK.

Vlookup across multiple values to return only one

4 responses

Similar discussions

Subscribe To Our Newsletter!