Help with a quick IF AND formula (I think)

[Paradigmgaming911]

Hello Excel Community,

I think I'm close to having this formula down, but I cannot figure out the ending of the formula to return the value I want ... hoping you can help


The attached spreadsheet involves candidates that receive multiple offers but only accept one (as noted by a value in the L column of INITIAL!)


I'm looking for a formula that will return results in FINAL! by matching the names in RSVP! and INITIAL! and then the resulting >0 value from column L in INITIAL!


The candidate, for example, Erica Reimers, had 3 interviews and accepted the Req# listed in L1 ... how do I return that value in FINAL! F2?


The FINAL! sheet shows the results as I would like them to appear


Appreciate the help!


Link to spreadsheet: Sample 1

 

[Coyotex3]

The VLOOKUP will only return the first value which in the case of Latasha James will be 0
See if this works.

Final Sheet

Sample 1.xlsx
	A	B	C	D	E	F
1	NAME					REQ #
2	Latasha James					2574202
FINAL

Cell Formulas
Range	Formula
F2	F2	=INDEX(INITIAL!$L$1:$L$12,AGGREGATE(15,6,(ROW(INITIAL!$L$1:$L$12)-ROW(INITIAL!$L$1)+1)/((INITIAL!$C$1:$C$12=FINAL!$A2)*(INITIAL!$L$1:$L$12<>"")),1))

Initial Sheet

Sample 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Erica	Reimers	Erica Reimers	(555)555-5555	eamreimers@gmail.com	Registered Nurse	NAMC	NICU	Peer: HM Scheduling	Night	Lauren	1455045
2	Erica	Reimers	Erica Reimers	(555)555-5556	eamreimers@gmail.com	Registered Nurse	NAMC	L&D	Offer	Night	Lauren
3	Erica	Reimers	Erica Reimers	(555)555-5557	eamreimers@gmail.com	Registered Nurse	NAMC	Postpartum	Offer	Night	Lauren
4	Tiara	Pack	Tiara Pack	(555)555-5558	ebonysmith568@gmail.com	Food Service Associate	NAMC	Kitchen	Offer	Night	Aliya	1307406
5	Jessica	Nichols	Jessica Nichols	(555)555-5559	nichols.jess07@gmail.com	Central Sterile Technician	NAMC	Oncology	Offer	Day	Thalia	1205460
6	Patricia	Greene	Patricia Greene	(555)555-5560	Patriciagreene409@gmail.com	Cook	NAMC	Kitchen	No Offer		Aliya	1475202
7	Brandon	Manor	Brandon Manor	(555)555-5561	manor_b12@yahoo.com	Food Service Associate	NAMC	Kitchen	Offer		Aliya
8	Latasha	James	Latasha James	(555)555-5562	ljames.lj1978@gmail.com	LVN	NAMC	4N	Interview Sheet Not Clear		Kessler
9	Latasha	James	Latasha James	(555)555-5563	ljames.lj1978@gmail.com	LVN	NAMC	3S	Offer		Kessler	2574202
10	Latasha	James	Latasha James	(555)555-5564	ljames.lj1978@gmail.com	LVN	NAMC	Oncology	Peer: HM Scheduling		Kessler
11	Minji	Hong	Minji Hong	(555)555-5565	michelleiominji@gmail.com	New Grad RN	NAMC	NICU	Peer: HM Scheduling	Night	Amber	1250252
12	Minji	Hong	Minji Hong	(555)555-5566	michelleiominji@gmail.com	New Grad RN	NAMC	Postpartum	Offer	Night	Amber
INITIAL

Cell Formulas
Range	Formula
C1:C12	C1	=A1&" "&B1

No way I was going to come up with such a hefty Formula lol.

 

[Paradigmgaming911]

Wow, that works. Amazing.

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[AhoyNC]

This formula would be shorter, but with your version of Excel I think you will have to enter it as an array formula with CTRL-SHIFT-ENTER.

=INDEX(INITIAL!$L$1:$L$12,MATCH(1,(A2=INITIAL!$C$1:$C$12)*(INITIAL!$L$1:$L$12<>""),0))