VLookup Multiple Values to Rows

[TrinaT]

Hey all,

I'm fairly certain a Vlookup won't work as I have multiple rows of the same data. I have a data export that I want to filter out for comms for some sales staff we have. I need to pull certain details, and i'm hoping that I can do this with formulas, rather than having to manually copy and paste, as there is a lot of data and I need to do it on a weekly basis.

I've done a snapshot of the raw data in rows 1-13, and the data outcome that I want to filter through in row 17. The values in columns B, C, E, F and H is what I need to pull from the raw data.

Any idea on which formula I need to use?

#VALUE!

Cell Formulas
Range	Formula
D2	D2	=CONCATENATE(B2," ",C2)
D17	D17	=IFS(C17="DL pitched",1,C17="SL pitched",0.5,C17="did not pitch",0)
G17	G17	=F17-E17

 

[awoohaw]

The data snap is not viewable. Can you use xl2bb to load the sample?

 

[TrinaT]

The data snap is not viewable. Can you use xl2bb to load the sample?

I've done that, it keeps coming up as #value...

#VALUE!

Cell Formulas
Range	Formula
D2	D2	=CONCATENATE(B2," ",C2)
D17	D17	=IFS(C17="DL pitched",1,C17="SL pitched",0.5,C17="did not pitch",0)
G17	G17	=F17-E17

 

[TrinaT]

#VALUE!

Cell Formulas
Range	Formula
E2:E13	E2	=CONCATENATE(C2," ",D2)
B16:B20	B16	=FILTER(E2:E13,A16=A2:A13)
C16:C20	C16	=VLOOKUP(B16,E2:L13,2)
D16:D20	D16	=IFS(C16="DL pitched",1,C16="SL pitched",0.5,C16="did not pitch",0)
E16:E20	E16	=VLOOKUP(B16,E2:L13,4)
F16:F20	F16	=VLOOKUP(B16,E2:L13,5)
G16:G20	G16	=F16-E16
H16:H20	H16	=VLOOKUP(B16,E2:L13,8)
Dynamic array formulas.

 

[awoohaw]

@TrinaT, you can use this link in the message boards to test the xl2bb tool:

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

 

[Peter_SSs]

I've done that, it keeps coming up as #value...

What is the exact name of the workbook that you tried to show with XL2BB in post #1?
In the code behind your post it is showing as Appointments w:e 22 Jan.xlsx which is not a valid workbook name (at least in Windows - don't know about Mac) - hence the XL2BB problem.
If your workbook name does contain that colon, try making a copy and name it without the colon then try XL2BB again.