URGENT vlookup/array or index help

[masond3]

Hey guys and girls

i need some help with a vlookup formula

I have created a ranking system which is attached file called “ranking logic”

i have created a column within logic1 called “vlookup2”
i need the vlookup to look at the mcc code “column c” on “logic1” then look at the “ranking logic” file sheet1, column A, and whatever the mcc code is on “logic1” i need it to return the number1 ranking seller code on sheet1, ranking logic

for instance mcc code 5712 on logic 1 , should look at sheet1, ranking logic, and return the seller code r05, as its ranked number1

for instance mcc code 5261 on logic 1 , should look at sheet1, ranking logic, and return the seller code r12, as its ranked number1

i hope this is clear

[benishiryo]

hi masond3, you can copy this, go to G2 of "logic1" & paste inside the formula bar:

Please Login or Register  to view this content.

then press CTRL + SHIFT + ENTER

let me know if it works

[masond3]

Benishiryo

Your formula, works a treat
However i am a little confused why you have referenced Query2'!$A$2:$A$194, could you explain that to me please ?

Also somewhere in your formula, is it possible to state that column B “rm sales band” is 2-4m, than apply the seller code R14 as default ?

[benishiryo]

Query2'!$A$2:$A$194 is the column i want to return (i.e. the RM code).

Also somewhere in your formula, is it possible to state that column B “rm sales band” is 2-4m, than apply the seller code R14 as default ?

hmmm not sure if i understand this. you mean instead of this "Query2'!$B$2:$B$194", show 2-4m? dont think i can. not sure about applying the seller code R14 as default too. is it something you can illustrate in a file?

[masond3]

HI Benishiryo
In the excel logic 1, if you look at column f , called (vlookup) it looks at the pcode sheet, and applies a seller to the postcode, however the postcode look up doesn’t work if the rm sales band is between 2-4m

I need a similar logic for vlookup2 column.

What you have done is correct, but i need to say that if rm sales band is between 2-4m then automatically assign it a seller code r15 .

If the sales band is 4m+ then use that ranking system

please find attached file, the column highlighted in red, is how the data should look

Hope this helps

[benishiryo]

ohhh i see~ try this then:

Please Login or Register  to view this content.

same thing. CTRL + SHIFT + ENTER to confirm

[masond3]

Hi Benishiryo

Thank you for the formula ,

at present the new formula just advises me of all of r15 within the 2-4m bracket ,with the rm bracket over 4m +the formula doesn’t apply and gives me n/a

Is it possible if you could merge the new formula with the old one ?

[homa5424]

Beni, I was looking for a similar solution this morning. Your formula was of great use. I tweaked it slightly to suit.

Thanks.

[benishiryo]

@masond3, so i suppose everything is fine after you did CTRL + SHIFT + ENTER? do mark the thread "Solved" if so. thanks

@homa5424, you are welcome. and thanks for the rep