Trying to use Vlookup with Index Match

mrichard

New Member
Joined
Mar 23, 2020
Messages
29
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Can you post a small sample of your data using the boards XL2BB addin please,
 
Upvote 0
Capture.PNG
 
Upvote 0
So I have another table on a different worksheet of which I have two columns. Column 1 could have any of the labor categories in col 1-5 and I want to write a formula that would pull in the results from col 6.

Sorry If I'm not real clear
 
Upvote 0
Book1
BCDEFGHIJ
3VehicleCAtVehicleCAtVehicleCAtVehicleCAtVehicleCAtERICAT
4Admin Assitant JrAdmin Assitant MIDAdmin Assitant SrAdmin Assitant IVAdmin Assitant VAdministrative Assistant
5Administrative Clerk 1Administrative Clerk 2Administrative Clerk 3Administrative Clerk 4Administrative Clerk 5Administrative Clerk
6ACL 1ACL 2ACL 3ACL 4ACL 5AC
7
8
9
10
11ACL 3AC
12
13
14
15
16
Sheet4
Cell Formulas
RangeFormula
D11D11=INDEX($H$4:$H$6,SUM(IFERROR((SEARCH(C11,$C$4:$G$6)^0)*(ROW($4:$6)-ROW($4:$4)+1),0)))
 
Upvote 0
Another option. Again entered with Ctrl-Shift + Enter and not just Enter.

Book1
CDEFGH
3VehicleCAtVehicleCAtVehicleCAtVehicleCAtVehicleCAtERICAT
4Admin Assitant JrAdmin Assitant MIDAdmin Assitant SrAdmin Assitant IVAdmin Assitant VAdministrative Assistant
5Administrative Clerk 1Administrative Clerk 2Administrative Clerk 3Administrative Clerk 4Administrative Clerk 5Administrative Clerk
6ACL 1ACL 2ACL 3ACL 4ACL 5AC
7
8
9
10
11
12Admin Assitant SrAdministrative Assistant
13
Sheet2
Cell Formulas
RangeFormula
D12D12=INDEX($H$4:$H$6,MATCH(1,MMULT(--($C$4:$G$6=C12),TRANSPOSE(COLUMN($C$4:$G$6)^0)),0))
 
Last edited:
Upvote 0
Thanks Mark858. I am still trying to get the CA_Punit formula to work. Its almost working. I will try yours next.

Thanks
Mark
 
Upvote 0
Results:
CA_Punit - I tried your formula and it somewhat worked but there were a few results that were incorrect. Not sure why..... And yep I hit Cntl-Shift Enter.
Mark858 - Your formula seems to work 100%.

If either of you have the time could you explain how your formulas work. It would be much appreciated.

Thanks
MRR
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top