VLOOKUP query

[excel_beta_345User]

I want to apply vlookup based on lookup value from column H for Column B to E all at once. Right now I can increment number in next column and do vlookup to get values but how can I do it once for all of those in one go ?

 

[saboh12617]

Hello,
On Excel 365 you can use XLOOKUP like so
In I2

=XLOOKUP(H2, $A$2:$A$26, $B$2:$E$26)

Then extend downwards.

 

[excel_beta_345User]

Hello,
On Excel 365 you can use XLOOKUP like so
In I2

=XLOOKUP(H2, $A$2:$A$26, $B$2:$E$26)

Then extend downwards.

It is giving me error

#N/A

 

[saboh12617]

Ah yes sorry i tested the formula with sorted values. You need to add the exact match parameter, like so

=XLOOKUP(H2, $A$2:$A$26, $B$2:$E$26, , 0)

 

[Peter_SSs]

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.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Using my smaller sample data the formula as posted in post #2 works for me - see columns N:R below. (The exact match parameter is the default in XLOOKUP)

However, you might also consider the formula in I2 as it does not need to be copied down and will return all results from that single cell formula.

25 01 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	ID	Name	Colour	Team	Date			ID	Name	Colour	Team	Date		ID	Name	Colour	Team	Date
2	One	Name 1	Red	Team 1	15/01/2023			Two	Name 2	Blue	Team 2	8/03/1997		Two	Name 2	Blue	Team 2	8/03/1997
3	Two	Name 2	Blue	Team 2	8/03/1997			One	Name 1	Red	Team 1	15/01/2023		One	Name 1	Red	Team 1	15/01/2023
4	Three	Name 3	White	Team 3	22/07/2015			Three	Name 3	White	Team 3	22/07/2015		Three	Name 3	White	Team 3	22/07/2015
5	Four	Name 4	Black	Team 4	3/12/2001			Six	Name 6	Yellow	Team 6	26/10/2020		Six	Name 6	Yellow	Team 6	26/10/2020
6	Five	Name 5	Green	Team 5	14/06/1989			Four	Name 4	Black	Team 4	3/12/2001		Four	Name 4	Black	Team 4	3/12/2001
7	Six	Name 6	Yellow	Team 6	26/10/2020			Five	Name 5	Green	Team 5	14/06/1989		Five	Name 5	Green	Team 5	14/06/1989
8
Sort

Cell Formulas
Range	Formula
I2:L7	I2	=SORTBY(B2:E7,MATCH(A2:A7,H2:H7,0))
O2:R7	O2	=XLOOKUP(H2, $A$2:$A$7, $B$2:$E$7)
Dynamic array formulas.

 

[RoryA]

Ah yes sorry i tested the formula with sorted values. You need to add the exact match parameter, like so

=XLOOKUP(H2, $A$2:$A$26, $B$2:$E$26, , 0)

Exact match is actually the default for XLOOKUP.

 

[excel_beta_345User]

Thank yo

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.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Using my smaller sample data the formula as posted in post #2 works for me - see columns N:R below. (The exact match parameter is the default in XLOOKUP)

However, you might also consider the formula in I2 as it does not need to be copied down and will return all results from that single cell formula.

25 01 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	ID	Name	Colour	Team	Date			ID	Name	Colour	Team	Date		ID	Name	Colour	Team	Date
2	One	Name 1	Red	Team 1	15/01/2023			Two	Name 2	Blue	Team 2	8/03/1997		Two	Name 2	Blue	Team 2	8/03/1997
3	Two	Name 2	Blue	Team 2	8/03/1997			One	Name 1	Red	Team 1	15/01/2023		One	Name 1	Red	Team 1	15/01/2023
4	Three	Name 3	White	Team 3	22/07/2015			Three	Name 3	White	Team 3	22/07/2015		Three	Name 3	White	Team 3	22/07/2015
5	Four	Name 4	Black	Team 4	3/12/2001			Six	Name 6	Yellow	Team 6	26/10/2020		Six	Name 6	Yellow	Team 6	26/10/2020
6	Five	Name 5	Green	Team 5	14/06/1989			Four	Name 4	Black	Team 4	3/12/2001		Four	Name 4	Black	Team 4	3/12/2001
7	Six	Name 6	Yellow	Team 6	26/10/2020			Five	Name 5	Green	Team 5	14/06/1989		Five	Name 5	Green	Team 5	14/06/1989
8
Sort

Cell Formulas
Range	Formula
I2:L7	I2	=SORTBY(B2:E7,MATCH(A2:A7,H2:H7,0))
O2:R7	O2	=XLOOKUP(H2, $A$2:$A$7, $B$2:$E$7)
Dynamic array formulas.

Thank you so much for this, it helped me immensely. I used sortby function as you suggested. And apologies for not using XL2BB, unfortunately that addon does not work due to company policies on O365.

 

[Peter_SSs]

Thank you so much for this, it helped me immensely.

You're welcome. Thanks for the follow-up.

apologies for not using XL2BB, unfortunately that addon does not work due to company policies on O365.

If you are not able to use XL2BB due to company policy then in any future threads you start, I suggest that you point that out in post 1 otherwise you will likely keep getting asked.

 

[saboh12617]

Exact match is actually the default for XLOOKUP.

Thank you for the precision, I assumed it was like the old V/Hlookups. My bad. Glad the problem is solved tho.