Trying to use Vlookup with Index Match

mrichard

New Member
Hi,

I use Vlookup and Index Match all the time but I am stumped on this problem.
I have a table that has approx 400 rows, 6 columns, columns 1-5 contain labor categories that I have mapped to column 6 labor categories.

Example I have the following table:
Col 1 (Project Manager Jr.) Col 2 (PM) Col 3 (Project Manager Level 1) Col 4 (Sr. PM) Col 5 (Project Manager Mid) : Col 6 (Project Manager)

So Col's 1-5 are all mapped and associated with Col 6.

I want to use a formula to look up a particular labor category in (col 1-5) and return the value of Col 6.
So if I lookup PM I want to see what it is mapped to in Col 6 which is Project Manager.
I hope this make sense.

I have tried everything I can think of : Vlookup, match, index, index/match etc.

Any thought out there?

Thanks
Mark

CA_Punit

Well-known Member
CA_Punit - I tried your formula and it somewhat worked but there were a few results that were incorrect. Not sure why.....

Can you post the data where it is returning incorrect result.

My formula is based on partial match. If say there are two Words ACL1 and ACL11 and both are under different category and if you search for ACL1, then it is quite probable that it might provide incorrect result.

However Mark858 result is based on Pure Match. So there is no chance of error.

Next time i will be careful. Considering Small data set i provided the easiest way.
To understand Mark formula you should know MMULT function pretty well.

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

MARK858

MrExcel MVP
--(\$C\$4:\$G\$6=C12) creates an array of True & Falses, the -- converts them to 1's and zero's
TRANSPOSE(COLUMN(\$C4:\$G\$6)^0)) the Column^0 part creates another array 5 columns wide by 1 row tall, the Transpose makes it 5 rows tall by 1 column.
The MMULT then does a Matrix multiplication on the 2 arrays
The Match then looks for a 1 in the result to get the position for the Index to look up.

CA_Punit

Well-known Member
You can search for Excelisfun Videos in youtube for better clarity in MMULT.

MARK858

MrExcel MVP
better clarity
Much as I like Mike's video's I think that depends on your preferred method of learning but if the OP does prefer video's then the link to the Excel is Fun video on MMULT is below (there are plenty of other video's available on the MMULT as well).

Replies
5
Views
106
Replies
11
Views
255
Replies
17
Views
319
Replies
19
Views
243
Replies
0
Views
66

1,128,128
Messages
5,628,861
Members
416,345
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back