Pulling loan # for specific employees into a different sheet.

[Cyndi2210]

Hi,

I am trying to show the value from the "Description" column from the "Loans" sheet into the "Data" sheet for the specific employee. Type 401L should populate Loan ID 1 and 401L2 should populate Loan ID 2. Any person without a loan should display as blank.

Thanks in advance for your help.

 

[awoohaw]

I just found a mistake in the Data sheet I had. I had Loan ID 1 in column B and not Column P
maybe this will work for you:

Book1
	A															P	Q	R	S
1	Employee #															Loan ID 1	Loan Payment 1	Loan ID 2	Loan Payment 2
2	102															80478	21.34
3	198
4	202															80479	5.75
5	216
6	220															80484
7	287															80488	21.07
8	609															80472	26.7	80473
9	665
10	705															80465	103.1	80464
11	1409															80486	26.22
12	1586															80462	70.03	80463	23.36
13	2709															80477	38.81	80476	35.18
14	3889															80466	40.51	80467	147.49
15	4676															80482	15.48	80483	34.96
16	5112
17	5239															80485	111.59
18	7741															80480	156.14	80481	166.21
19	8018
20	8146															80470	32.45
21	8418															80468	37.83	80469	77
22	8869															80474	26.62
23	8883
24	9045															80471	42.69
25	9358															80475	74.82
Data

Cell Formulas
Range	Formula
P2:P25,R2:R25	P2	=IFERROR(INDEX(Loans!$D$2:$D$27,MATCH($A2&IF(P$1="Loan ID 1","401L","401L2"),Loans!$A$2:$A$27&Loans!$B$2:$B$27,0)),"")

 

[Cyndi2210]

I started a new worksheet used the copy feature in the upper right hand corner of your worksheet in Post #4, then I tried to create the pages as you mentioned above. It didn't work. So then I took your suggestion to just paste it into a new workbook and the highlighted columns were blank. I just hit Ctrl+V when I pasted in both times. The formulas are there. My cursor is in B4.

FYI, when I remove the IFERROR & ,"") I get a #Value! Error

 

[awoohaw]

I can't tell if you copied the formulas down from B4 to B27 and D4 to D27.

If you did, have you used the EVALUATE FORMULA tool on the Formula Ribbon to step through the calculation.

You can also debug one step at a time (using your worksheet posted in the image in Post #12), in B4 by slowly building up your formula by testing each of these sub subsections
(no guarantee this is typo free - but I hope you understand what I'm asking you to do):
1. Match($A4,$H$:$H$29,0) drag it down to confirm you're getting matches, if you're not then the data has some non printing characters.
2. Match("401L",$I$4:$I$I29,0), drag, confirm you have matches.
3. IF(B$3="Loan ID 1","401L","401L2"), drag, confirm, if no match then B3 has some non printing characters in it, retype "Loan ID 1" into the B3, see if it matches.
4. Match(IF(B$3="Loan ID 1","401L","401L2"),$I$4:$I$I29,0), drag, confirm you get some appropriate hits (you may get 401L2 numbers if it is not correct).
5. Match($A4&IF(B$3="Loan ID 1","401L","401L2"),$H$4:$H$29&$I$4:$I$29,0), drag, confirm you get appropriate hits.
6. INDEX($K$4:$K$29,Match($A4&IF(B$3="Loan ID 1","401L","401L2"),$H$4:$H$29&$I$4:$I$29,0),0), drag, confirm
7. IFERROR(INDEX($K$4:$K$29,Match($A4&IF(B$3="Loan ID 1","401L","401L2"),$H$4:$H$29&$I$4:$I$29,0),0),""), drag, confirm
8. Copy to column D, drag confirm.

 

[Cyndi2210]

I can't tell if you copied the formulas down from B4 to B27 and D4 to D27.

If you did, have you used the EVALUATE FORMULA tool on the Formula Ribbon to step through the calculation.

You can also debug one step at a time (using your worksheet posted in the image in Post #12), in B4 by slowly building up your formula by testing each of these sub subsections
(no guarantee this is typo free - but I hope you understand what I'm asking you to do):
1. Match($A4,$H$:$H$29,0) drag it down to confirm you're getting matches, if you're not then the data has some non printing characters.
2. Match("401L",$I$4:$I$I29,0), drag, confirm you have matches.
3. IF(B$3="Loan ID 1","401L","401L2"), drag, confirm, if no match then B3 has some non printing characters in it, retype "Loan ID 1" into the B3, see if it matches.
4. Match(IF(B$3="Loan ID 1","401L","401L2"),$I$4:$I$I29,0), drag, confirm you get some appropriate hits (you may get 401L2 numbers if it is not correct).
5. Match($A4&IF(B$3="Loan ID 1","401L","401L2"),$H$4:$H$29&$I$4:$I$29,0), drag, confirm you get appropriate hits.
6. INDEX($K$4:$K$29,Match($A4&IF(B$3="Loan ID 1","401L","401L2"),$H$4:$H$29&$I$4:$I$29,0),0), drag, confirm
7. IFERROR(INDEX($K$4:$K$29,Match($A4&IF(B$3="Loan ID 1","401L","401L2"),$H$4:$H$29&$I$4:$I$29,0),0),""), drag, confirm
8. Copy to column D, drag confirm.

It turns out it was a version issue. The IT person upgraded my system to 365 & the formula worked perfectly.

Thanks so much for sticking with me.

 

[awoohaw]

It turns out it was a version issue. The IT person upgraded my system to 365 & the formula worked perfectly.

Thanks so much for sticking with me.

What version did you have before? The functions in the formulas I had given have been around since Excel 2007 or 2010.
Regardless, I'm happy you got your formula working.

Best wishes!

 

[Cyndi2210]

What version did you have before? The functions in the formulas I had given have been around since Excel 2007 or 2010.
Regardless, I'm happy you got your formula working.

Best wishes!

I had 2016, but I also had a VLookup that was not working. One of my co-workers running 365 tried it on her machine & it worked.

 

[awoohaw]

I had 2016, but I also had a VLookup that was not working. One of my co-workers running 365 tried it on her machine & it worked.

Okay, maybe there was something wrong with the installation or an update that took some functionality out. But now you have a much better tool kit! Update your profile here to say you have 365. There is so much you'll be able to do. Just learning the LET () function will make your excel life much better!

 

[Asbestos_Jen]

FYI: I'm running Excel 2007, and the formulas from post#11 only worked for me after array entry.

 

[awoohaw]

FYI: I'm running Excel 2007, and the formulas from post#11 only worked for me after array entry.

That's good to know. But, surprising. The functions IFERROR, IF, MATCH, and INDEX. Match and Index have been around for decades. IFERROR became available in 2007. I wonder what makes it think it is an array formula. Very interesting. Maybe it is the concatenated arguments in the MATCH function, but, I was doing that in 2003.

 

[Asbestos_Jen]

That's good to know. But, surprising. The functions IFERROR, IF, MATCH, and INDEX. Match and Index have been around for decades. IFERROR became available in 2007. I wonder what makes it think it is an array formula. Very interesting. Maybe it is the concatenated arguments in the MATCH function, but, I was doing that in 2003.

My guess was the concatenated ranges as well.