Match then vlookup with 2 nested if functions

[groud]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 958"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]G1[/TD]
[TD]H1[/TD]
[TD]I1[/TD]
[TD]J1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Type[/TD]
[TD]result[/TD]
[TD]vlookup result[/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Return Code[/TD]
[TD]Line Item[/TD]
[TD]Low Tolerance[/TD]
[TD]High Tolerance[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]9101[/TD]
[TD]1010[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1010[/TD]
[TD]Cash[/TD]
[TD]9100[/TD]
[TD]9119[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]9182[/TD]
[TD]1050[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1020[/TD]
[TD]Taxes Receivable[/TD]
[TD]9120[/TD]
[TD]9139[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]L[/TD]
[TD]9467[/TD]
[TD]2010[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1030[/TD]
[TD]Account Receivable[/TD]
[TD]9150[/TD]
[TD]9169[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]L[/TD]
[TD]9488[/TD]
[TD]2030[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1040[/TD]
[TD]Inventory[/TD]
[TD]9170[/TD]
[TD]9179[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]R[/TD]
[TD]2200[/TD]
[TD]3020[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1050[/TD]
[TD]Prepaids[/TD]
[TD]9180[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]R[/TD]
[TD]5100[/TD]
[TD]3080[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1060[/TD]
[TD]Due From[/TD]
[TD]9140[/TD]
[TD]9149[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]E[/TD]
[TD]1900[/TD]
[TD]4010[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]2010[/TD]
[TD]Salaries[/TD]
[TD]9460[/TD]
[TD]9479[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]E[/TD]
[TD]5326[/TD]
[TD]4070[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]2020[/TD]
[TD]Accounts Payable[/TD]
[TD]9420[/TD]
[TD]9429[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD]2030[/TD]
[TD]Unearned Revenue[/TD]
[TD]9480[/TD]
[TD]9499[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD]2040[/TD]
[TD]Retainage Payable[/TD]
[TD]9430[/TD]
[TD]9439[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD]2060[/TD]
[TD]Due to[/TD]
[TD]9400[/TD]
[TD]9419[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3010[/TD]
[TD]Local Revenue[/TD]
[TD]1000[/TD]
[TD]1999[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3020[/TD]
[TD]Intermediate Revenue[/TD]
[TD]2000[/TD]
[TD]2999[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3030[/TD]
[TD]State Revenue[/TD]
[TD]3000[/TD]
[TD]3999[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3040[/TD]
[TD]Federal Revenue[/TD]
[TD]4000[/TD]
[TD]4999[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3050[/TD]
[TD]Other Sources[/TD]
[TD]5000[/TD]
[TD]5099[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3060[/TD]
[TD]Transfers In[/TD]
[TD]5200[/TD]
[TD]5299[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3070[/TD]
[TD]Sale of Capital Assets[/TD]
[TD]5300[/TD]
[TD]5399[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3080[/TD]
[TD]LTD Proceeds[/TD]
[TD]5100[/TD]
[TD]5199[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]3999[/TD]
[TD]Beginning Fund Balance[/TD]
[TD]5400[/TD]
[TD]5499[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]4010[/TD]
[TD]Instruction[/TD]
[TD]1000[/TD]
[TD]1999[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]4020[/TD]
[TD]Support[/TD]
[TD]2000[/TD]
[TD]2999[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]4030[/TD]
[TD]Community Services[/TD]
[TD]3000[/TD]
[TD]3999[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]4040[/TD]
[TD]Facilities and Construction[/TD]
[TD]4000[/TD]
[TD]4999[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]4050[/TD]
[TD]Debt Service[/TD]
[TD]5100[/TD]
[TD]5199[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]4060[/TD]
[TD]Transfers Out[/TD]
[TD]5200[/TD]
[TD]5299[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD]4070[/TD]
[TD]Capital Outlay[/TD]
[TD]5300[/TD]
[TD]5499[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Ok, So I want to lookup the type column in row B "A,L,R,E" and match it with the type column in column F, then return the "return code" for the code in column B that falls between the two tolerance columns in column I and J. I want this to be the equation in column D (which has the correct values that I would like it to return).

For example if the value in column B is "E" and the code in column C is "5201", I'd like the value returned to column D to find the match in column F of "E" and find the row that the code falls between the two tolerances, in this case "e" and "5201" fall within row 28 where the return code would be "4060"

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Try

D3
=IF(B3<>"",INDEX($G$3:$G$29,MATCH(1,INDEX(($F$3:$F$29=B3)*(C3>=$I$3:$I$29)*(C3<=$J$3:$J$29),0),0)),"")
copy down

M.

 

[groud]

You sir, are a lord. Thanks. I've only been trying to figure this on out for the last 2 days. Where did you do your learning?

I originally got "#N/A " in D3 ... then I copied down and it worked for those cells. But still not D3. So I changed the code in C3 and then D3 for some reason worked.

 

[Marcelo Branco]

You sir, are a lord. Thanks. I've only been trying to figure this on out for the last 2 days. Where did you do your learning?

I originally got "#N/A " in D3 ... then I copied down and it worked for those cells. But still not D3. So I changed the code in C3 and then D3 for some reason worked.

You are welcome and thanks for the feedback

M.
ps: I suspect that there was extraneous spaces in C3, because the formula worked as expected when you has inserted a new value..