Forum Discussion
Solved
If cell contains specific text from one column, return value located in the same row
Hello,
In the first sheet I have a table to calculate product prices, in the second sheet there is a list with product codes in one column and product price next to it. What I am trying to do is to type product code in the first sheet and I want to return the product price value next to the code so I can work further with this data.
This formula works for me:
=IF(B6=Sheet2!B2;Sheet2!D2;0)
The problem is that this formula works when I type P-01(first product located in the cell B2 on the second sheet), P-02 works if I type it in the cell B7 and so on. But if I type P-02 in the cell B6 - it does return 0 as the price value.
The question is how to improve this formula to be able to type specific product code in any cell of the column and get a product price which is in the same row next to the specific product code.
- Set the LinkedCell property of the combo box to the address of a cell on the worksheet, for example to the cell under the combo box. In the example below, this is A2. When you select an item from the dropdown list, it will be entered in the linked cell.
- Create a list with the services in the first column, and the corresponding prices in the second column. You can use the first column as the ListFillRange of the combo box. In the example, it is E2:E6.
- In the cell where you want the price, enter a VLOOKUP formula (in combination with IFERROR): =IFERROR(VLOOKUP(A2,$E$2:$F$6,2,FALSE),"")
2 Replies
=IFERROR(VLOOKUP(B6,Sheet2!$B$2:$D$1000,3,FALSE),"product missing in sheet2")
You can try a lookup formula. If the product doesn't exist in Sheet2 the formula returns a message.
- OliverScheurich
Thank you!
