VLOOKUP with nested IF statements

[SWHarmon79]

I need to nest a few IF statements into a VLOOKUP and I am having issue figuring out how to get this right. I have looked through a number of related post but haven't found one describing my situation.

This is my current formula =IFERROR(VLOOKUP(A7,ElliotsPricingUpdate,6,0),"")

The table that this is looking at is a material price update sheet. It uses the industry standard E,C,M for 1, 100 and 1000. I need to nest IF statements that will take the E,C or M from column 6 and return either 1, 100 or 1000.

Any help would be much appreciated. Thanks

 

[jtakw]

Hi,

If I understand correctly, this should help:

=IFERROR(LOOKUP(VLOOKUP(A7,ElliotsPricingUpdate,6,0),{"C","E","M"},{100,1,1000}),"")

Note the Lookup Vector needs to be in Alpha order.

 

[SWHarmon79]

Thanks!!! That worked perfectly. I was definitely going about that in the wrong way.

 

[jtakw]

You're welcome, welcome to the forum.

A Nested IF would also work, but it's going to get a little lengthy..

Also, if the list of values (i.e. E, C, M) may get longer/larger, you can use a table reference instead of "hard-coding" in the formula.