Trying to use Vlookup with Index Match

mrichard

New Member
Joined
Mar 23, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
894
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
--($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
Joined
Nov 18, 2019
Messages
894
Office Version
  1. 365
Platform
  1. Windows
You can search for Excelisfun Videos in youtube for better clarity in MMULT.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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).

 

Watch MrExcel Video

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top