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]

Hi and welcome to Mr.Excel,

Is this for Google Sheets or do you need an Excel formula?

 

[Paradigmgaming911]

Hi and welcome to Mr.Excel,

Is this for Google Sheets or do you need an Excel formula?

It's for Excel ... i thought it would be easier to work with an actual sheet than an image tho.

 

[Coyotex3]

Hmmm, Maybe this formula will work for what you need.

=VLOOKUP(A2,Initial!C1:L12,10,FALSE)

Given that the data is included in the "Initial" tab, do we need the RSVP tab for this to work at all?

Also, Erica Reimers is misspelled on the "Final" tab. This might be why the formula you were working on did not work

 

[Paradigmgaming911]

Hmmm, Maybe this formula will work for what you need.

=VLOOKUP(A2,Initial!C1:L12,10,FALSE)

Given that the data is included in the "Initial" tab, do we need the RSVP tab for this to work at all?

Also, Erica Reimers is misspelled on the "Final" tab. This might be why the formula you were working on did not work

The RSVP tab is where we first get their information, then we keep track of their status in the INITIAL tab ... but because there are multiple name entries in INITIAL, I was trying to make the formula read something in verbiage like this:

Find all of the circumstances in INITIAL where the name matches the name in RSVP (Erica RSVP'd and has three name entries on INITIAL). However many names match her name on INITIAL, look in those rows for the only result in column INITIAL! 'L' that has a value in it. So Erica had three interviews, but only one value in column L, and that's the value I was trying to pull into FINAL 'F'

I just couldn't figure out how to tell the formula that there may be multiple entries in the name field for INITIAL, and as long as they match the name in RSVP, then look in all of the results for the only one that listed a value in L

I get confused even thinking about how horribly I'm explaining it

 

[Paradigmgaming911]

but so appreciate you even looking at it for me. I'll try your formula

 

[Paradigmgaming911]

Wow, that worked!

I need to get smarter and learn more! Thank you very much!

 

[Coyotex3]

Thanks for the feedback. I believe it will not work for Latasha James though. The reason this formula will not work there is because the first row with her name is empty on Column L. Trying to see if I can get a better formula for you. You are using Excel 2016 right?

 

[Paradigmgaming911]

Thanks for the feedback. I believe it will not work for Latasha James though. The reason this formula will not work there is because the first row with her name is empty on Column L. Trying to see if I can get a better formula for you. You are using Excel 2016 right?

yes

 

[AhoyNC]

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