VLOOKUP using dependent lists

Question

I'm really struggling to get this to work. I have a data set of different types of steel, the size and weight. Example shown below:

A            B            C
Section    Designation   Weight(kg/m)
UB         127x76x13     13
UB         152x89x16     16

On a separate sheet I have a drop down list for "Section", then in the next column there is a drop down for "Designation" which is dependent on my choice of "Section". In my 3rd column I want to use a VLOOKUP to populate the "Weight" after I have chosen my "Designation" but all I can get is an N/A error.

My formula is as follows: =(VLOOKUP,D12,Sheet1!A1:C1158,3,FALSE)

Help me please!!

I'm not sure if this solves your question, but in your formula I see that VLOOKUP is referring to relative references instead of absolute ones: A1:C1158, while I would expect A$1:C$1158. — Dominique, Aug 12 '16 at 08:17

krib · Answer 1 · 2016-08-12T08:42:46.270

0

I would use following formula:

=INDEX(Sheet1!A:C,MATCH(D12,Sheet1!B:B;0),3) You can add the lines to A:C if you like

edited Aug 12 '16 at 08:42

answered Aug 12 '16 at 08:27

krib

569
4
14

score 0 · Answer 2 · answered Aug 20 '16 at 17:55

I suggest inserting a new ColumnA immediately to the left of Section, which I am taking as presently in A2 of Sheet1. Then in A1 and copied down to suit:

=B1&" | "&C1

For your separate sheet, to look up values from dropdowns in D2 and E2:

=VLOOKUP(D2&" | "&E2,Sheet1!A:D,4,0)

There may be alternatives but it is not clear from your question whether, for example, a single Designation might apply to more than one Section. In the circumstances a unique key combining Section and Designation is safer.

VLOOKUP using dependent lists

2 Answers2