Vlookup across multiple values to return only one

[Closed]
Report
Posts
7
Registration date
Wednesday July 31, 2013
Status
Member
Last seen
December 2, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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 If

nextstep:
Cells(k, "G") = y
GoTo nextk
nextm:
Next m
nextk:

Next k

MsgBox "macro over"
End Sub


1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.
Posts
7
Registration date
Wednesday July 31, 2013
Status
Member
Last seen
December 2, 2013

Thanks venkat - I thought I would complicate it. And the sample file cells don't relate to the ones I put in the original post - the sample file was an after thought pulled from my existing data set which I can see how it confused the situation even more so apologies! :D

However, lets take the sample file and ill try and clarify.

I want G3 to look at the values in D3, E3 & F4 and compare them to everything in column H. If it finds a match (which it will do in H15), I want it to display the contents of I15, J15 & K15. Actually for the purposes of this, lets just say I want G3 to show J15 as I can fiddle with it to show the description & status etc later.

The reason column D,E & F exist is because the scanned part codes (column B & C) have various formats so my thought was that i have to extract the right numbers from the part codes and this had to be done in separate cells. Then with the three variations of possible correct parts, thats when it does a lookup etc

i can do vlookups no problem - what i cant do is look up three different values from three different cells, knowing that one of the three will be the right one.

Clear as mud?! :D
Posts
7
Registration date
Wednesday July 31, 2013
Status
Member
Last seen
December 2, 2013

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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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.