Forum Discussion
How to LookUp a Value based on 1 drop down and 1 criteria from a different worksheet with 2 columns
I am trying to find the correct formula with the following details:
On Sheet 1, I want to find what the price should be, based on 2 things:
1. Sheet 1 - A drop down with 2 options ("Roanoke" or "Lineage") in cell D29; and
2. The item # in D21 (Sheet 1); (List of Item #s are on Sheet 2, Column B)
On Sheet 2, I have 2 columns: 1 says "Roanoke", and 1 says "Lineage"
Based on the item # (Manually entered on Sheet 1 - D21) and based on which drop down is chosen (Roanoke or Lineage) - Sheet1:
WHAT IS THE FORMULA TO LOOK UP THE PRICE ON SHEET 2 under Columns T and U (Lineage and Roanoke, respectively) based on the drop down chosen and the Item # Manually entered on Sheet1 D21?
Use Index and Match Functions:
=IFERROR(INDEX(Sheet2!T2:U6,MATCH(D21,Sheet2!B2:B6,0),MATCH(D29,Sheet2!T1:U1,0)),"NoPrice")FikturFox - Unfortunately, this did not work.
D29 is a text, not a number so I'm wondering if that's the issue?
- Also, Sheet2 isn't a table. I don't know if that matters.
- Hi. It should work well whether D29 is a text or a number. It is best to share a sample workbook.
