Struggling with a specific VLOOKUP

[jaxstraww]

This VLOOKUP has two columns of data combined into one and I'm struggling with the formula to solve.

In column A I have Employee ID
In column B I have Hire Date
In column C I have a unique identifier
In column D I have Age
In column E I have separation column
In column F I have Age
In column G I have Hire Date + Employee ID

Looking to solve for Age in Column D

 

[hagia_sofia]

Since you have 365, would this work for you:

=XLOOKUP(--(B2:B5&A2:A5),G2:G5,F2:F5)

 

[NickFru]

Hello - you could also try;

=IFERROR(INDEX($F$2:$F$5,MATCH("*"&B2&"*",$G$2:$G$5&"",0)),"")

 

[jaxstraww]

Since you have 365, would this work for you:

=XLOOKUP(--(B2:B5&A2:A5),G2:G5,F2:F5)

I made a mistake when I edited my profile. I have 2016 PRO. When I ran your formula I got a #NAME? error.

 

[jaxstraww]

Hello - you could also try;

=IFERROR(INDEX($F$2:$F$5,MATCH("*"&B2&"*",$G$2:$G$5&"",0)),"")

Hey Nick, appreciate the formula but that looks way more advanced than I'm hoping. Also looking to be able to do this myself much like a standard VLOOKUP. The key is to solve with a VLOOKUP> Fine with an XLOOKUP I think if I can get it to work being they are close in name at least.

 

[Cubist]

For older version, try INDEX/MATCH.

Book1
	A	B	C	D	E	F	G
1	Emp ID	Hire Date	ID	Age		Age	Hire Date + Emp ID
2	5055	11012020	1	50		50	110120205055
3	4055	11012020	2	45		65	110120203055
4	3055	11012020	3	65		45	110120204055
5	2055	11012021	4	23		23	110120212055
Sheet10

Cell Formulas
Range	Formula
D2:D5	D2	=INDEX($F$2:$F$5,MATCH(B2&A2,$G$2:$G$5,0))

 

[NickFru]

Ah no problem at all Jaxstraww - yes, no doubt there will be a way to solve this through using both Vlookup and Xlookup. I could have actually used the Vlookup with the match instead of Index with the match but within the match element of the formula, it looks complicated because we are extracting only part of the reference we are looking up, hence using "*"&B2&"*" and then using IFERROR at the start just in case you want a blank result in case of an error.

Take care.

 

[jaxstraww]

For older version, try INDEX/MATCH.

Book1
	A	B	C	D	E	F	G
1	Emp ID	Hire Date	ID	Age		Age	Hire Date + Emp ID
2	5055	11012020	1	50		50	110120205055
3	4055	11012020	2	45		65	110120203055
4	3055	11012020	3	65		45	110120204055
5	2055	11012021	4	23		23	110120212055
Sheet10

Cell Formulas
Range	Formula
D2:D5	D2	=INDEX($F$2:$F$5,MATCH(B2&A2,$G$2:$G$5,0))

Appreciate the quick reply. Getting an error message. Checked the formula and all looks correct. Wonder if I have a formatting error someplace with the numbers. Using format "number" for columns B & G.

 

[jaxstraww]

Appreciate the quick reply. Getting an error message. Checked the formula and all looks correct. Wonder if I have a formatting error someplace with the numbers. Using format "number" for columns B & G.

Ignore my stupidity. I had to add an ' to column G. It was the scientific notation causing me grief.

 

[Cubist]

Glad you figured it out. Cheers.