CODEWITHSUNDEEP
excelvba
Vickar
Vickar

Reputation: 953

Lookup values from 3 columns and return corresponding cell value from 4th column

I'm basically working on an excel sheet that provides the service advisors at a car service station with spare part numbers by accepting 3 chars(4th,8th & 10th char) from the VIN(Vehicle Identification Number) and returns corresponding part number from the table. The table looks like this,

     A              B            C             D            E          F

Char#4InVIN   Char#8InVIN   Char#10InVIN    OIL-FILTER  AIR-FILT    AC-FILT
    C              B              E         2630035504  281131R100  97133AQ000
    C              B              F         263203CAA0  281131R100  971332E210
    C              D              G         2630002503  281131R200  97133AQ000
    C              A              H         2630002503  281131R100  971332E210
    D              C              E         2630035504  281133X000  971332H001
    D              E              G         2630035504  28113F2000  97133F2000

The user will give input in the below format,

            4th Char    8th Char    10th Char
Input          C            B           E

The output should be,

        Oil-Filter  Air-Filter  Ac-Filter
Output  2630035504  281131R100  97133AQ000

I'm able to achieve the output by taking the 8th and the 10th char by writing the following formula for each output cells(consider cell E17 to be input for 8th char and F17 to be for 10th char)

{=INDEX(D2:D10,MATCH($E$17,IF($C$2:$C$10=$F$17,$B$2:$B$10),0))}

But I'm stuck at applying lookup for all 3 chars entered by user and display corresponding values. Any suggestions ?? I tried the following code, but couldn't generate the expected output

{=INDEX(D2:D10,MATCH($E$17,IF(AND($C$2:$C$10=$F$17,$A$2:$A$10=$D$17),$B$2:$B$10),0))}

Upvotes: 0

Views: 1761

Answers (2)

Mrig
Mrig

Reputation: 11702

In Cell K2 enter

=INDEX(D$2:D$7,MATCH(1,($A$2:$A$7=$H2)*($B$2:$B$7=$I2)*($C$2:$C$7=$J2),0))

Drag/copy above formula down and across (to right) as required. This is an array formula so commit it by pressing Ctrl+Shift+Enter. See image for reference.

enter image description here

Upvotes: 0

rwilson
rwilson

Reputation: 2145

You need to concatenate your inputs and have them look-up against a concatenated table. I entered this formula in cell "B9" shown in the image below. You are going to need to adjust your ranges and then array enter it with ctrl+shift+enter:

=INDEX(D$2:D$4,MATCH($B$8&$C$8&$D$8,$A$2:$A$4&$B$2:$B$4&$C$2:$C$4,0))

enter image description here

Upvotes: 1

Related Questions