Forum Discussion
Formula Help - Match List
I am in need of some assistance on a formula for a cell.
I'll try to explain this without attaching a document. I would be happy to attach the document but this post doesn't allow that. I am trying to create a document to create estimates of cost for projects.
I have 2 sheets.
Sheet 1 = Item List
| A | B |
| ITEM | COST |
| Soda | $1.00 |
| Water | $2.00 |
| Tea | $3.00 |
Sheet 2 = Project Estimate
| A | B | C | D |
| ITEM | COST | QUANTITY | TOTAL |
| X | Y | ||
On Sheet 2 I have used data validation on ITEM column to list the items found from Sheet 1 ITEM. This drop-down is shown as X in the example. The formula I need is for Y is pull the cost from Sheet 1 depending on the selection made for X. The only field I want a user selecting from is X, and I want Y to auto populate.
I can continue to provide more information if needed. I appreciate any and all help in creating this formula.
Thank you.
=INDEX('Sheet 1'!$B$2:$B$4,MATCH('Sheet 2'!$A2,'Sheet 1'!$A$2:$A$4,0))You can try INDEX and MATCH.
Sheet 1:
Sheet 2:
4 Replies
=INDEX('Sheet 1'!$B$2:$B$4,MATCH('Sheet 2'!$A2,'Sheet 1'!$A$2:$A$4,0))You can try INDEX and MATCH.
Sheet 1:
Sheet 2:
This looks to have worked perfectly. Is there an additional part of this formula (or a conditional format) that will leave a cell blank if nothing is selected for X?
=IFERROR(INDEX('sheet 1'!$B$2:$B$4,MATCH('sheet 2'!$A2,'sheet 1'!$A$2:$A$4,0)),"")
You can try this formula. The INDEX and MATCH formula is wrapped into IFERROR.
