Vlookup, iferror and sumifs query?

[LesleyL]

I am really not good at Excel and am battling to finish my formula. I apologise in advance if this has been answered before but I can't fidn the right formula.

The first part of my formula works really well. It basically looksup a Trade Discount percentage (from a named table) and delivers to the right cell, however, I want to be able to show a blank cell in both Price increase and Trade Discount if there is no cost price entered.

Apparently Vlookup only looks right and this field happens to be on the left. I tried to add the LEN & IF & SumIfs but can't get any of them right.

[TABLE="width: 500"]
<tbody>[TR]
[TD]COST PRICE
[/TD]
[TD]PRICE INCREASE 2017
[/TD]
[TD]TRADE DISCOUNT
[/TD]
[TD]INSTALLATION COSTS
[/TD]
[TD]TOTAL COST PER BLIND
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=IFERROR(VLOOKUP($T$3,suppliers,2,0),"")
[/TD]
[TD]=IFERROR(VLOOKUP($T$3,suppliers,3,0),"")
[/TD]
[TD]=IFERROR(VLOOKUP($F12,$Y$13:$AC$27,4,0),"")
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="class: xl67, width: 96"] =IFERROR(ROUND(S12*(1+$T12)*(1-$U12),2)+$V12,"")
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]This is entered manually
[/TD]
[TD]This looks at a field where I select a supplier from drop down list and the associated price increase from supplier
[/TD]
[TD]This looks at a field where I select a supplier from drop down list and the associated trade discount from supplier
[/TD]
[TD]This looks at a field where I select a product from drop down list and the associated installation cost is input
[/TD]
[TD]This works out the total COST price of the blind.
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I now want to add a formula that if the cell to the left "Cost price" is blank, then this field remains blank. However, the minute I input the supplier in the drop down cell, it automatically puts in the percent increase. Even though I have added the Iferror formula???
[/TD]
[TD]I now want to add a formula that if the cell to the left "Cost price" is blank, then this field remains blank. However, the minute I input the supplier in the drop down cell, it automatically puts in the trade discount.
[/TD]
[TD]This formula works fine.
[/TD]
[TD]This formula works fine.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Base something on this, I cant provide cell references as I dont know what range "suppliers" is set to.

=IF(OFFSET(B2,INDEX(C2:C4,MATCH(B1,B2:B4,1),1)-1,-1)="","",INDEX(C2:C4,MATCH(B1,B2:B4,1),1))

B1 is supplier name
B2 is top of supplier list
B2:B4 is supplier list
C2:C4 is price
A2:A4 is cost price

This will return blank is cost price of supplier is blank

UPDATE: Alternatively copy the cost price to the right of the suppliers range into a new column and modify your VLOOKUP.
Your formula should be IF(VLOOKUP()="","",VLOOKUP), not really IFERRROR
IFERROR will only occur if the supplier is not on the list

 

[LesleyL]

Thank you Special-K99 but i can't get either of them to work. Is there anyway I could attach my spreadsheet to share so you can see what I am doing wrong? I'm really, really stupid with Excel and have bitten off more than I can chew .

Base something on this, I cant provide cell references as I dont know what range "suppliers" is set to.

=IF(OFFSET(B2,INDEX(C2:C4,MATCH(B1,B2:B4,1),1)-1,-1)="","",INDEX(C2:C4,MATCH(B1,B2:B4,1),1))

B1 is supplier name
B2 is top of supplier list
B2:B4 is supplier list
C2:C4 is price
A2:A4 is cost price

This will return blank is cost price of supplier is blank

UPDATE: Alternatively copy the cost price to the right of the suppliers range into a new column and modify your VLOOKUP.
Your formula should be IF(VLOOKUP()="","",VLOOKUP), not really IFERRROR
IFERROR will only occur if the supplier is not on the list

 

[Special-K99]

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

I would prefer a spreadsheet to an image so I dont have to retype existing data.

 

[LesleyL]

Once again, thank you Special-K99. I have figured a work around which seems to be working ok. I have moved my Lookup cells to a different sheet and named all the ranges. I have also hidden these columns so that it's not so messy but formulas still use those columns. Not sure if this is the right way in Excel but it's working for a dum-dum like me. .

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

I would prefer a spreadsheet to an image so I dont have to retype existing data.