VBA learner ITG

Active Member
Joined
Apr 18, 2017
Messages
267
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
If the returned value always starts with a capital letter, try
=CODE(LEFT(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)))-64
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Store NoStore NameXMS Grade W1RETURNED VALUE
6952TEST STORE 1A11
2587TEST STORE 2A21
1930TEST STORE 3A1
132TEST STORE 4A41
3036TEST STORE 5B92
4006TEST STORE 6B2
657TEST STORE 7C3
1207TEST STORE 8C113
1190TEST STORE 9D5004

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


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.
 
Upvote 0
Did you actually try the formula I suggested? It will return the same values that you have shown.
 
Upvote 0
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:
Store NoStore NameXMS Grade W1RETURNED VALUE
6952TEST STORE 1A120
2587TEST STORE 2A220
1930TEST STORE 3A20
132TEST STORE 4A420
3036TEST STORE 5B930
4006TEST STORE 6B30
657TEST STORE 7C40
1207TEST STORE 8C1140
1190TEST STORE 9D50050

<tbody>
</tbody>
20
 
Upvote 0
To return those values you can change the formula to
=(CODE(LEFT(VLOOKUP($A7,'CHRISTMAS GIFT SHOP'!$A:$E,3,FALSE)))-63)*10
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top