How can i return Multiple values from a data set using a Lookup Value(I want to spill the value, No Vlookup)

ajesh27

New Member
Joined
Mar 5, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have a data set like below

1616297085397.png


when select the Emplyee id, i need the the Name and the department value

1616297221785.png


like below shoud be the result

1616297302630.png


New Dynamic array fomulas or Xlookup is preferred. NO Vlookup or index match.

Thanks for the help in advance
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Is that data set in a formal Excel table (ListObject) or just a normal range?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
 
Upvote 0
Is that data set in a formal Excel table (ListObject) or just a normal range?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
Just normal range not tables
 
Upvote 0
Is that data set in a formal Excel table (ListObject) or just a normal range?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
Just normal range not tables, the values reside in Non Adjacent coloums
 
Upvote 0
I know that you said no Index/Match but why not when that will spill as you want?

21 03 21.xlsm
ABCDE
1Employee IDNamePhoneMailDept
2EMP 1N1P1M1D1
3EMP 2N2P2M2D2
4EMP 3N3P3M3D3
5EMP 4N4P4M4D4
6EMP 5N5P5M5D5
7EMP 6N6P6M6D6
8
9
10Employee IDNameDept
11EMP 5N5D5
ajesh27
Cell Formulas
RangeFormula
B11:C11B11=INDEX(B2:E7,MATCH(A11,A2:A7,0),{1,4})
Dynamic array formulas.
 
Upvote 0
I know that you said no Index/Match but why not when that will spill as you want?

21 03 21.xlsm
ABCDE
1Employee IDNamePhoneMailDept
2EMP 1N1P1M1D1
3EMP 2N2P2M2D2
4EMP 3N3P3M3D3
5EMP 4N4P4M4D4
6EMP 5N5P5M5D5
7EMP 6N6P6M6D6
8
9
10Employee IDNameDept
11EMP 5N5D5
ajesh27
Cell Formulas
RangeFormula
B11:C11B11=INDEX(B2:E7,MATCH(A11,A2:A7,0),{1,4})
Dynamic array formulas.
I heard Xlookup can do every thing that Vlookup and Index-match can , so searching for that
 
Upvote 0
I heard Xlookup can do every thing that Vlookup and Index-match can , so searching for that
This can be done with XLOOKUP (& INDEX) but it is basically the same as, and no shorter than, INDEX/MATCH.

21 03 21.xlsm
ABCDE
1Employee IDNamePhoneMailDept
2EMP 1N1P1M1D1
3EMP 2N2P2M2D2
4EMP 3N3P3M3D3
5EMP 4N4P4M4D4
6EMP 5N5P5M5D5
7EMP 6N6P6M6D6
8
9
10Employee IDNameDept
11EMP 5N5D5
ajesh27 (3)
Cell Formulas
RangeFormula
B11:C11B11=INDEX(XLOOKUP(A11,A2:A7,B2:E7),{1,4})
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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