Vlookup with If Functions

[vegobu]

Hi experts,

I have an issue of which I need to pick a band value for the relevant pack size if the value is fall in certain range per pack size.
I have attached the sample of my file.

Thanking in advance for the assistance provided.

Vegobu

[vipul.halbe]

U seem to be multiplying lot size with price relating to the respective alphabet.
Am I correct?

[vegobu]

Yes you are right, but this is the table I created so that when my colorant values falls in a certain range for certain pack size, then the band value (band peso) to that appropriate range to be picked.

[Kalithro]

Good god, this one kicked my butt! Would have been 10x easier if Microsoft would have just added =Evaluate. I would have just used the replace function on your "0<n<=20" data. But no this makes it text.
Never the less... try this.

[vegobu]

Thanks pal, really appreciate your help.

[Teethless mama]

Try this:

No helper columns is required

G3: =LOOKUP(2,1/(Size=D3)/(--LEFT(Range,FIND("<",Range)-1)<=F3),Band)

copy down

See the attached file

[Kalithro]

Try this:

No helper columns is required

G3: =LOOKUP(2,1/(Size=D3)/(--LEFT(Range,FIND("<",Range)-1)<=F3),Band)

copy down

See the attached file

Or better use this one. Not 100% sure I understand how this works.

[vegobu]

Hi
Thats great pal.

Thank you very much