X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Match, Index, Office 2003    
post Jan 12 2018, 07:00 AM

Posts: 384
Joined: 20-January 04

I have a workbook, which contains 2 sheets. The first one contains two columns, Part, and Description. The second contains 3 columns, Part, Description, Drawing. What I want to do, is, in the Part column of sheet 1, select a part from a dropdown(which I've already created), and the description is completed automatically, by matching the part number to the description, in the second sheet. The part number in Sheet 1 is in A1, the part numbers in sheet 2 are in A2:A64, and the descriptions, B2:B64. I tried, =IF(A1="","",INDEX('Part List'!A:A,MATCH(A1,B2:B64,0))), but I get an error.
Go to the top of the page
post Jan 12 2018, 07:33 AM

Posts: 42
Joined: 18-August 16
From: Bristol, UK

It is probably simpler to use VLOOKUP in this situation:
=VLOOKUP(A1,'Part List'!A2:B64,2,TRUE)
Go to the top of the page
post Jan 12 2018, 08:13 AM

Posts: 384
Joined: 20-January 04

Thanks. That's done it.
Go to the top of the page
post Jan 12 2018, 08:58 AM

Posts: 135
Joined: 5-March 14

From your explanation and equation

=IF(A1="","",INDEX('Part List'!A:A,MATCH(A1,B2:B64,0)))

it looks like you are trying to lookup/match the part number in the Description column (B). The second input to the MATCH command should be "A2:A64".

I agree VLOOKUP will also work, but I want to respectfully remind you that the cells in the lookup vector must be in ascending order. Otherwise Excel will give you an incorrect result. The advantage of INDEX-MATCH is that the lookup vector is order independent. So if you add new records later, you do not need to remember to re-sort the array.


Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    18th March 2018 - 05:47 PM