vlookup help

[masond3]

Hi guys

Hoping you will be able to help

i have pulled some rawdata from access and i need to analyse this by certain criteria which is on the other sheets

Basically on the raw data page, there are some postcodes, on the pcde sheet, i have assigned a sellers to postcodes

i need to do a vlook up on the raw data tab, which looked at the pcode sheet, then provides me with the sellers name

the next step is that if rm_sales_band is between 2m~+4m, ignore the postcode vlookup and automatically apply the name “Jason”

The next stage is that there is another worksheet attached called query4
Query 4, looks at different mcc codes, (which is also presented in the rawdata sheet on query3). Query 4 sheet1, basically looks at the mcc code, counts how many there is and applies a ranking system.
for eg mcc code 5621 , it advises me that rmcode r05, deals with mcc code more often than r13. As r13 is ranked 4th

eg mcc code 5968, r05 deals with that mcc more than r10.

I no that in query4 i havent included all the mcc codes, it was just some dummy data i was applying my formula to.

Within the excel doc query3, i have coloured in two columns, this is the data that i should be seeing.
Hope this helps if you have any questions please get back to me

[Ishtiyaq]

Hi,

I got a fix for the first part of your query, you can try this formula. Type this is cell F2 of Raw data sheet.

=IF(B2="2M to 4M","Jason",IF(ISNA(VLOOKUP(LEFT(D2,2),Pcode!$A$1:$C$125,3,0)),VLOOKUP(LEFT(D2,1),Pcode!$A$1:$C$125,3,0),VLOOKUP(LEFT(D2,2),Pcode!$A$1:$C$125,3,0)))

Some of your post codes are starting with single letter instead of 2, so thats the reason I used left function for 2 and 1 character respectively.

Please elaborate the second part of your issue, the sheet 4 you've attached is not displaying any data, its reflecting REF error. Also let me know if this formula resolves your issue if not specify whats missing or required.

Warm regards
Ishtiyaq

[masond3]

hi ishtiyaq

the second part of the formula i have worked out, the formula is =INDEX('[Ranking logic.xlsx]Query2'!$A$2:$A$194,MATCH(C2&1,'[Ranking logic.xlsx]Query2'!$B$2:$B$194&'[Ranking logic.xlsx]Query2'!$F$2:$F$194,0))

thank you for your help

[Ishtiyaq]

Hi Masond,

I'm still not able to see the data, the error is still there. I guess the reference file is on your computer since I can't access it, Im getting that error but its working fine on your system.

You should mark this thread as solved as your query is solved now.

Warm regards
Ishtiyaq