using IF formula with VLOOKUP for different columns

[laurenb]

So I'm a novice but slowly getting the hang of this.

I currently am working off of two tabs in a workbook. One lists products and prices, the other is a table where you can enter products and the corresponding price shows up (using vlookup).

I'm wondering if it's possible to use IF formulas so that when a product is entered, if a quantity is over 5, the VLOOKUP looks in a different column.

For example, this is the code I have right now to find the price based on the part number:

=IFERROR(VLOOKUP(A6,'0919.1'!1:1048576,2,FALSE),0)

It is set to look for prices listed in column 2 on the price list.

(I use the if-error because if there is nothing listed I need it to say $0.00)

I would like to have a separate IF formula that uses VLOOKUP to find prices listed in column 6 if the quantity is higher than a certain number. I've found some things, but I always end up with #REF or FALSE instead of a price (=IF(A6>5,VLOOKUP(A6,'0919.1'!1:1048576,6,FALSE)) is one I tried)

Is this doable?

[#2 · Accepted solution]

@laurenb If the result of the first VLOOKUP is > 5, then something like this should work:

=IFERROR(IF(VLOOKUP(A6,'0919.1'!A:N,2,FALSE)>5,VLOOKUP(A6,'0919.1'!A:N,6,FALSE),VLOOKUP(A6,'0919.1'!A:N,2,FALSE)),0)

You'll need to adjust your column references to suit.

HTH

[#3]

@Smitty Smith 

Thanks for responding!

yeah I'll play around with it! it's defaulting to column 6 lol

[#4]

@Smitty Smith 

Is there also a formula to add so that if the redirect column (6) is empty, it goes back to column 2?

I tested it and it works great as you gave it to me! The only issue is that if there is no price for increased quantity it defaults to 0 instead of defaulting back to column 2.

I tried a few workarounds, but I keep getting errors.

[#5]

@laurenb This is probably a bit overkill, but seems to work OK:

=IFERROR(IF(VLOOKUP(A6,'0919.1'!A:N,2,FALSE)>5,IF(VLOOKUP(A6,'0919.1'!A:N,6,FALSE)=0,VLOOKUP(A6,'0919.1'!A:N,2,FALSE),VLOOKUP(A6,'0919.1'!A:N,6,FALSE)),VLOOKUP(A6,'0919.1'!A:N,2,FALSE)),0)

[#6]

@Smitty Smith 

I had to tweak it a little bit for what I needed, but this worked great!

Thanks so much dude you saved me a huge headache here nothing I tried was working.

[#7]

@laurenb Glad I could help! Have a great Thanksgiving!