Trying to use Vlookup with Index Match

[mrichard]

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]

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.

 

[MARK858]

--($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.

MMULT in Excel

What does MMULT Do in Excel? MMULT, a Math & Trigonometry function in Excel, stands for "Matrix Multiplication." It returns the product of two arrays

www.wallstreetmojo.com

 

[CA_Punit]

You can search for Excelisfun Videos in youtube for better clarity in MMULT.

 

[MARK858]

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).