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

Look up and extract data

0

I have a part number in a cell,in the table i am trying to extract data from the same part number is split across 2 cells.

i want to use VLOOKUP or something but can't find a way.

I do not want to start using formulas to get table info to match.

can someone help please .?

Answer
Discuss

Answers

0
Selected Answer

If you are more adventurous, you can do this without a helper column, using an array formula. For example

=INDEX($A$1:$C$60,MATCH($A1&$B1,$A$1:$A$60&$B$1:$B$60, 0),3)

You may have heard of Index/Match. If you haven't, look it up on a site like 'Contextures.com'. In essence, the INDEX() function specifies a range, just like VLOOKUP, and returns a cell's value specified by row and column. =INDEX(A1:C60, 10, 3) would return the value in the 10th row, 3rd column of A1:C60, meaning C10.

MATCH($A1&$B1 gives the lookup value, in your case the two parts of your part number. These values could be from another worksheet, even another workbook, from outside the lookup range, or from witin the range as my example has it.

The MATCH() function carries out a lookup by saying, "give me the row number where the value of A1:A60 = [Lookup value].

In the INDEX/MATCH combination you use the MATCH function to return the row number for the INDEX function and then specify the column as you do in VLOOKUP. To make this method work for multiple criteria you have to make it an array formula, meaning it calls itself multiple times instead of only once, each time looking for another criterion.

In the above formula, $A$1:$C$60 is the range from which to return a value. The "3" at the end specifies the 3rd column for trhe INDEX function. Between $A$1:$C$6 and "3" is the MATCH function.

$A$1:$A$60 & $B$1:$B$60 specifies that the first ($A$1) is to be found in $A$1:$A$60 and the second creterion from the other range, $B$1:$B$60. This is the part that needs the functionality of array formulas.

You enter an array formula in the formula bar (you can also enter it in the cell but I find the formula bar easier to understand). Whereas, with normal formulas, you finish your entry with 'Enter', an array formula requires you to press Shift+Contol+Enter, all three keys simultaneously. If you do this correctly you will see the formula surrounded with curly braces in the formula bar, like {=INDEX($A$1:$C$6,MATCH($A1&$B1,$A$1:$A$60&$B$1:$B$60, 0),3)}. If you don't see the braces the formula will return a #VALUE error.

If there is a chance that the MATCH function returns no match, embed the entire above formula in a IFERROR() function.

Discuss

Discussion

Thank you very much thats exactly what i need :)
Chester (rep: 2) Nov 8, '17 at 4:20 pm
Add to Discussion
0

Easiest way is to make a third helper column in the source data and combine the two columns.

For example:

=A1 & B1

Then copy that formula down the column for the entire data-set and you can run the vlookup on it.

Discuss


Answer the Question

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