Help with IF Statement imbedded in VLOOKUP

[Atwell19]

Hello, I believe I have a unique problem and would appreciate it any help that is brought my way.

My task: I would like to see what range an employee would fall in based on their grade and the amount of monthly sales. On sheet A i would have the persons grade and their actual sales. On sheet B I would have grades (1-5) and the the different ranges of sales for that grade. I would like to be able to write a statement that looks up a persons grade and their actual monthly sales on sheet A, then goes to sheet B finds the persons grade and returns the range number based on the amount of actual sales they have. I have provided an example to help:

Example

Sheet A

Name Grade Sales

A2 4 26,499.92

Sheet B

Grade(A1) Range 1(B1) Range 2(C1) Range 3(D1) Range 4(E1)

1 20802 22094 23386 26145
2 22361 23847 25333 28322
3 24049 25746 27443 30682
4 25179 27454 29728 33236
5 26169 29187 32205 36515

Expected results for example above would be Range 2 with a grade of 4 and monthly sales less than $27,454 but more than $25,179.


I have been trying to imbed an if statement inside a vlookup but i am not having any luck. Any suggestions or help would be much appreciated.

Thanks.

[Special-K]

This works

=MATCH(B2,INDIRECT("SheetB!A"&VLOOKUP(A2,SheetB!A1:A5,1,0)&":"&"E"&VLOOKUP(A2,SheetB!A1:A5,1,0)),1)

[Atwell19]

Thank you for the help, however I am having trouble with the indirect function listed above.

[scott.s.fower]

its a bit long but it will do what you want

=INDEX(Sheet2!$B$2:$E$6,VLOOKUP(Sheet1!B2,Sheet2!$A$2:$A$6,1,0),IF(B2=1,MATCH(C2,Sheet2!$B$2:$E$2,1)+1,IF(B2=2,MATCH(C2,Sheet2!$B$3:$E$3,1)+1,IF(B2=3,MATCH(C2,Sheet2!$B$4:$E$4,1)+1,IF(B2=4,MATCH(C2,Sheet2!$B$5:$E$5,1)+1,IF(B2=5,MATCH(C2,Sheet2!$B$6:$E$6,1)+1))))))

hope you can follow along

[Atwell19]

Thank you Scott. Is there a way to get the result of Range 2 instead of the numerical value?

[scott.s.fower]

actually, i like the indirect formula

I think his formula above is a bit off, but based on your original post I think I have the formula that you want. if you use my original method, to add the ability to get the range, it would be a very long formula

="Range"&" "&IF(MATCH(C2,INDIRECT("Sheet2!A"&VLOOKUP(B2,Sheet2!$A$1:$A$6,1,0)+1&":"&"E"&VLOOKUP(B2,Sheet2!$A$1:$A$6,1,0)+1),1)>=5,4,MATCH(C2,INDIRECT("Sheet2!A"&VLOOKUP(B2,Sheet2!$A$1:$A$6,1,0)+1&":"&"E"&VLOOKUP(B2,Sheet2!$A$1:$A$6,1,0)+1),1)) <---this formula is in cell D2

just change your sheet names to match the sheets in your workbook

example.png

[Atwell19]

Wow Scott, great work thank you.

[scott.s.fower]

if you're satisfied with the answer make sure you click thread tools, mark as solved at the top of the thread, only you can do it since you created the thread