VLookup, Match or Index? Thanks in advance for your help Sites are listed A2:A50

Question

VLookup, Match or Index?

Thanks in advance for your help

Sites are listed A2:A50 and are used as data valadation for entries from row 101 down

Equipment Categories are listed B2:B10 and are used as data valadation for entries from row 101 down

Work Categories are listed C2:C4 and are used as data valadation for entries from row 101 down

The order numbers are listed for each work category E2:E50, F2:F50, and G2:G50

From row 101 downover I would like to populate columb E with the correct order number based on the validated information in A & C

Therefore: if A101= an entry in A2:A50 and C101= an entry in C2:C4 lookup the associacted order number listed in E2:E50, F2:F50, and G2:G50

Venue Equipment Category Work Category Month Proactive Maintenance (PPM) Reactive Maintenance (Labour) Parts Only Contract & Non-Contract Botanic Garden Catering Proactive Maintenance January A045506 A045501 A045508 Business School Refrigeration Reactive Maintenance February A044698 A044701 A044703 Cafe on The Green Warewash Parts Only March A045378 A045507 A045509 Calman's Cafe April A045494 A045492 A045490 Chemistry Cafe May A045494 A045492 A045490 Collingwood Total June A045367 A045368 A045366 Greys Total July A045467 A045466 A045468 Hatfield August A045514 A045522 A045513 Hild & Bede September A045432 A045438 A045435 Library Cafe October A045494 A045492 A045490 Palintine Centre November A044795 A044793 A044794 Queens December A043850 A043851 A043849 Riverside Cafe, Dunelm Total A045146 A045147 A045148 St Aidans A045889 A044846 A044843 St. Cuthberts A045424 A045426 A045431 St.Marys A043557 A043560 A043558 The Graham Sports Centre (Maiden Castle) A043584 A043580 A043579 Trevelyan A043862 A043932 A043933 University (College) A045411 A045413 A045412 Ushaw College A045879 A045880 A045882 Van Mildert A043856 A043857 A043858 YUM Van Venue Equipment Category Work Category Month Work Completed Order Number Business School Catering Proactive Maintenance September A044698

Explanation / Answer

..Since the entries in rows 101+ are already validated through data validation, the only thing left to do is compute the correct column to get the order number from. This can be done with the MATCH function:*^&^$

=VLOOKUP(A101,$A$2:$G$50,4+MATCH(C101,$C$2:$C$4,0),FALSE)

Note that Column B does not appear in the formula, so if you need to be sure of a valid entry in that cell also, you'll need to add an IF function. e.g:

=IF(B101<>"", above_formula, error_message or null_string)

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.