Complex Lookup Formula

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
252
Office Version
  1. 2016
Platform
  1. Windows
I am looking to work out a formula that will allow me to use the Index & If function. At least that's what I would like to use to get the job down. So on sheet 2, I have a list of names in a2 and on b2 I have Primary secondary and so on. On sheet 1 I have a search query based on the name. If I put a name It will return the job that that, that person has as a primary job. However, if the name appears twice in the same search area, meaning they have two jobs I need the second cell to display the second job. If the name is not listed twice in the first column then it will display the job in the second column. If there is no name to be found the Iferror will display "no job".

Short version If there is no name in A2 it should display no name selected, if the work has two jobs in the first column the first needs to be displayed in one cell and the other in the second, and so on for the rest of the other cells. If the name does not have any jobs next to it then it would display no job. Project is below.

Project
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
252
Office Version
  1. 2016
Platform
  1. Windows
Update on the formula I used on sheet one. I changed the COUNTBLANK($A$2)=1 to Isblank function
+
 

Watch MrExcel Video

Forum statistics

Threads
1,127,590
Messages
5,625,674
Members
416,125
Latest member
NeedExcelHelp2021

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