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]

In that case can you please explain everything that you need, as I am not willing to continue guessing what may or may not work.

 

[VBA learner ITG]

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.

Hi Eric,

Thank you for posting this. This does what I need without writing a complex formula but by adding an allocations table.

 

[VBA learner ITG]

In that case can you please explain everything that you need, as I am not willing to continue guessing what may or may not work.

Hi Fluff,

I really appreciate your help today as your formula logic has resolved another issue. I do appreciate your assistance.

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[Eric Golf]

Ditto