Formula advice

[VBA learner ITG]

Hi all,

Apologies if this has been asked before but i cannot find an answer to my question on here or on google.

I have the below formula which is a standard vlookup which returns a certain value found for A, B, C etc

=IF(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)="A",1,IF(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)="B",2,IF(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)="C",3,IF(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)="D",4))))

However, when I try and apply a wildcard to the "A" to return any value with A because the returned vaue can be A1, A2 and so on it doesnt work.

I have tried various approaches and i am stumped and need your expert advice.

 

[Fluff]

If the returned value always starts with a capital letter, try
=CODE(LEFT(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)))-64

 

[VBA learner ITG]

If the returned value always starts with a capital letter, try
=CODE(LEFT(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)))-64

Thank you for your help.

Apologies, i fogot to mention the numberical value might change.

on my example formula i have done a generic A = 1, B=2 etc

When I have to amend the returned value across different returned vlookup values i need to allocate a different value for example A = 30, B = 60 etc

this is why I am trying to utilise a wildcard search.

 

[Fluff]

I don't understand why a wildcard would help to change the 1 to 30 or the 2 to 60.
Can you post some sample data along with the expected result & explain how you get that result.

 

[VBA learner ITG]

I don't understand why a wildcard would help to change the 1 to 30 or the 2 to 60.
Can you post some sample data along with the expected result & explain how you get that result.

This is the lookup table:
[TABLE="width: 503"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Store No[/TD]
[TD]Store Name[/TD]
[TD]XMS Grade W1[/TD]
[TD]RETURNED VALUE[/TD]
[/TR]
[TR]
[TD="align: right"]6952[/TD]
[TD]TEST STORE 1[/TD]
[TD]A1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2587[/TD]
[TD]TEST STORE 2[/TD]
[TD]A2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1930[/TD]
[TD]TEST STORE 3[/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD]TEST STORE 4[/TD]
[TD]A4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3036[/TD]
[TD]TEST STORE 5[/TD]
[TD]B9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4006[/TD]
[TD]TEST STORE 6[/TD]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]657[/TD]
[TD]TEST STORE 7[/TD]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1207[/TD]
[TD]TEST STORE 8[/TD]
[TD]C11[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1190[/TD]
[TD]TEST STORE 9[/TD]
[TD]D500[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]


My current formula works by finding all the A, B, C and i can assign the 1, 2, 3 as the returned value instead of the A,b,c

What i want to achieve the same principle when its letter and numberical value such A1 i would return as 1 without writing a complex formula.

I have a database which i have to apply this QTY to a store grading and the qtys in the formula can chnage day in and out depending on what qtys of items being sent.

 

[Fluff]

Did you actually try the formula I suggested? It will return the same values that you have shown.

 

[VBA learner ITG]

Did you actually try the formula I suggested? It will return the same values that you have shown.

Yes, I did. The issue i have encountered is that the requested return value could be per below when i come to apply the formula again on the next piece of work when i have to send out different QTY to that store by grade.


The problem I have is that i constantly apply a lookup formula each time to and send out different values to a store based on its grading structure.

This is the lookup table:
[TABLE="class: cms_table, width: 503"]
<tbody>[TR]
[TD]Store No[/TD]
[TD]Store Name[/TD]
[TD]XMS Grade W1[/TD]
[TD]RETURNED VALUE[/TD]
[/TR]
[TR]
[TD="align: right"]6952[/TD]
[TD]TEST STORE 1[/TD]
[TD]A1[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]2587[/TD]
[TD]TEST STORE 2[/TD]
[TD]A2[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]1930[/TD]
[TD]TEST STORE 3[/TD]
[TD]A[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD]TEST STORE 4[/TD]
[TD]A4[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]3036[/TD]
[TD]TEST STORE 5[/TD]
[TD]B9[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]4006[/TD]
[TD]TEST STORE 6[/TD]
[TD]B[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]657[/TD]
[TD]TEST STORE 7[/TD]
[TD]C[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]1207[/TD]
[TD]TEST STORE 8[/TD]
[TD]C11[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]1190[/TD]
[TD]TEST STORE 9[/TD]
[TD]D500[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]
20

 

[Fluff]

To return those values you can change the formula to
=(CODE(LEFT(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)))-63)*10

 

[VBA learner ITG]

Hi

I appreciate you explaining your code further, now it makes sence.

If for example I had to send the same QTY's to A & B grade this is where i have an issue and the original formula resolves this issue.

 

[Eric Golf]

H VBA Learner ITG,

Do you need nested lookups so the values returned are easily changed? e.g Set up a list in G1 to H4 as below:-
A 20
B 30
C 40
D 50

then your formula could be:-

Untested -
=VLOOKUP(LEFT(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE),1),$G$1:$H$4,2,0)

This will return the value associated with the first character in column C for the store number looked up in column A.

You can then amend the values in column H as required.

Hope this helps

Eric.