UtterAccess.com
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    
 
   
robUK
post Jan 12 2018, 07:00 AM
Post#1



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
 
Vince
post Jan 12 2018, 07:33 AM
Post#2



Posts: 36
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
 
robUK
post Jan 12 2018, 08:13 AM
Post#3



Posts: 384
Joined: 20-January 04



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



Posts: 134
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.

HTH

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th January 2018 - 10:56 PM