New Contributor

VLOOKUP with IF criteria

I need to do VLOOKUP in a document with 2 pricelists.

I have 4 columns.

Part number (manual entry)   Description (vlookup)   Pricelist (NEED TO CHOOSE BETWEEN 2) Discount (VLOOKUP)

The costing sheet looks for data from 2 different sheets, within the same document.

Discount looks vlookup info from a sheet

I need the pricelist column, to look for data from another sheet, based on the result achieved in discount column.

EXAMPLE

ABC gets 25% discount, the price in the costing sheet, needs to come from column 5 from pricelist sheet

XYZ gets no discount, the price in the costing sheet (SAME COLUMN), needs to come from column 4 from the pricelist sheet

5 Replies

Re: VLOOKUP with IF criteria

Why not upload a sample file along with the desired output mocked up manually so that it would be easy to understand what exactly you are trying to achieve?

Re: VLOOKUP with IF criteria

@Subodh_Tiwari_sktneer Hi, thank you, attached simplified version of the document.

4 sheets

1st sheet cover and customer view

2nd costing sheet to place the formula in the columns

3rd price list with 2 columns, yellow is reseller the other list pricing

4th sheet customer info to allocate data to 1st and 2nd page regarding discount

Discount % will determine which column the price will come from.

Re: VLOOKUP with IF criteria

Thanks for the file. Unfortunately I am not able to visualize your requirement by reading your description in the original post and by looking at the sample file. Maybe you need to rephrase your requirement more clearly. While describing your requirement, use exact sheet names, column numbers and headers.
To describe it more clearly, fill the Part# in column A in Costing Sheet for few rows and once your existing formulas return values, then explain which formula in which column you would like to tweak and the logic behind it.

Re: VLOOKUP with IF criteria

I'm not certain what your goal is for this formula. However, you did mention VLOOKUP with IF criteria possibly being needed. If you need to consider multiple criteria and the return is a number, perhaps you'll want to use SUMIFS.

Re: VLOOKUP with IF criteria

Hi,
thank you,
I have Sheet 1, that needs to reflect customer details, this is all fine
Sheet 2 is my costing and calculations sheet
Column A - Manual data entry of required part number
Column B - Will, via VLOOKUP function, populate the relevant description, (from sheet 3 information Column B), for the part no. in column A
Column C - via VLOOKUP my costing that will populate (from sheet 3 information Column C)
Column D - HERE I NEED THE HELP - this column needs to choose from 2 column pricing (from sheet 3 information Column D & F)
Column F - is info the COLUMN D will use to chose between value from sheet 3 information Column D & F - if the value is 25% it needs to pull the price from column E, if the value is NOT 25% it will pull the value from column D on sheet 3
Sheet 3 is my price list with 2 columns, D & E, column D needs to choose between the column D & E values, once criteria has been met =25% OR NOT = 25%