Match then vlookup with 2 nested if functions

[groud]

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

<tbody>
</tbody>

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..