Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vlookup, with IF and with IF it begins with these 6 first digits then lookup at certain table based on the first 6 digit.

0

This is my first time in here posting i hope I explain this correctly.

I have a Excel file with 4 sheets, the first 3 sheets contains thousands of barcodes and information of 3 different vendors. The last sheet contains only barcodes of all 3 vendors. I am trying to pull information based on the barcodes on the 4 sheet and the only thing I came up with is that all of them start with 6 different codes and finish differently, total digits are different too, between 10-12 characters.

So I was thing doing a VLOOKUP, with IF it starts with ?????? go to this table and then returning the value I want. But I cannot figure it.

So in sheet 4.

VLOOKUP A2 and If A2 starts with 848983, go to table called (ABC) and find the exact total match and return column 5, IF it starts with 877184 go to table (ABC2) and find the exact total match and return column 6, IF it starts with 8714692 go  to table (ABC3) and find the exact match and return column 3.

Now I can combine the first 3 sheets to a master file but because they were downloaded from different programs and contain different number of columns, etc. it will be a huge undertaking combining them. But that would me my last resource.

Please help and I hope I explain it good. Thank you!

excelvlookup
MS Office Version: excel 2013
posted by: LGuevara10 on Tue Sep 13, 2016 at 11:50 am
Answer
Discuss

Answers

0

The "best" way to do it is to put it all on one sheet.

However, there are a number of ways to search through multiple sheets and you can use a complex formula but screw that, use a UDF (User Defined Function) instead.

Use this one:

Function VLOOKUPWORKBOOK(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
'Vlookup function that will search all worksheets in the workbook - however, the data table that is being searched must be in the same
'location on every worksheet.

Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets

With wSheet

Set Tble_Array = .Range(Tble_Array.Address)

vFound = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_look)

End With

If Not IsEmpty(vFound) Then Exit For

Next wSheet
Set Tble_Array = Nothing

VLOOKUPWORKBOOK = vFound

End Function

It works like a Vlookup function that searches the entire workbook. You can read about this UDF here: Vlookup Function UDF (this is from our site)

The one thing to note is that the left column of each table on each worksheet should be in the same column.

If you don't know anything about UDF's, read our tutorial on creating UDF's in Excel

posted by: don (rep: 1989) on Tue Sep 13, 2016 at 2:32 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login