lookup using multiple criteria

E

Eddie Bauer

I have a spreadsheet of part numbers and their descpritive
characteristics, as in the sample below:
Model Description width Part No
D10 Extreme Service 28.0" 9W4465
D10 Extreme Service 32.0" 9W5828
D10 Moderate Service32.0" 9W5826
D10 Moderate Service36.0" 9W5825
D10R Extreme Service 24.0" 7T724
D10R Extreme Service 28.0" 7T4607
D10R Extreme Service 30.0" 6Y2534
D10R Extreme Service 32.0" 7T4605

I want to lookup the part number, which is unique, by
using the model, description, and width as search
criteria. Thanks for any assistance
 
A

Aladin Akyurek

Let A1:C9 house the sample, including the labels/fields.

In D2 enter & copy down:

=A2&CHAR(127)&B2

Let E2 house the lookup value: D10 Extreme Service, and F2: 32.0"

In G2 enter:

=INDEX($C$2:$C$9,MATCH(E2&CHAR(127)&F2,$D$2:$D$9,0))

to fetch Part No 9W5828
 
A

Alan Beban

For anyone to whose workbook the functions in the freely downloadable
file at http://home.pacbell.net/beban are available, with data in A2:D9,
Model in F1, Description in G1, width in H1

=columnvector(arrayrowfilter2(arrayrowfilter1(arrayrowfilter1($A$2:$D$9,1,$F$1),2,$G$1),3,$H$1),4)

Alan Beban
 
M

Mike

A B C D
1 Model Description width Part_No
2 D10 Extreme Service 28.0" 9W4465
3 D10 Extreme Service 32.0" 9W5828
4 D10 Moderate Service 32.0" 9W5826
5 D10 Moderate Service 36.0" 9W5825
6 D10R Extreme Service 24.0" 7T724
7 D10R Extreme Service 28.0" 7T4607
8 D10R Extreme Service 30.0" 6Y2534
9 D10R Extreme Service 32.0" 7T4605

F G H
10 Model Description width
11 D10 Moderate Service 36.0"

1. Select Range A1:D9
2. Choose [Data]->[Filter]->[Advanced Filters]
3. Move your mouse cursor into the [Criteria] box and drag Range F10:H11
4. Hit Enter key.
 
Top