从3列查找值并从第4列返回相应的单元格值(Lookup values from 3 columns and return corresponding cell value from 4th colum

从3列查找值并从第4列返回相应的单元格值(Lookup values from 3 columns and return corresponding cell value from 4th column)

我基本上正在制作一张excel表，通过从VIN（车辆识别号码）接受3个字符（第4，第8和第10个字符），在汽车服务站为服务顾问提供备件号，并从中返回相应的部件号。表。 该表看起来像这样，

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

用户将以下面的格式提供输入，

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

输出应该是，

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

通过为每个输出单元格编写以下公式，我可以通过获取第8个和第10个字符来实现输出（考虑单元格E17输入第8个字符，而F17输入第10个字符）

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

但我坚持应用查找用户输入的所有3个字符并显示相应的值。 有什么建议么 ？？ 我尝试了以下代码，但无法生成预期的输出

{=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))}

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))}

最满意答案

您需要连接输入并让它们查找连接表。 我在下图所示的单元格“B9”中输入了这个公式。 您将需要调整范围，然后使用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))

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))