检查单元格是否符合多个条件 [英] Check whether a cell matches multiple criteria

查看：82
本文介绍了检查单元格是否符合多个条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在努力格式化VLOOKUP以使其按我需要的方式工作.#N/A到处都是.

我可能完全不正确地使用了某些东西，而不是预期的.

我有以下数据:

  Windows版本表-AA2:AD3450硬件型号栏-P2:P85接受的SKU-M2:M55客户名称-Sheet1单元格A2:A9000客户端模型-Shee1 Cell J2:J9000客户区域-Sheet1单元格B2:B9000客户端操作系统-Sheet1单元E2:E9000

我需要确定以下内容

  • 如果"Windows版本"位于客户端操作系统"中，并且硬件模型"与客户端模型"匹配，则将客户端名称"输出到AG列，将客户端区域"输出到AH列，并将客户端操作系统"输出到AI列

我希望这有点道理.我一直试图弄清楚这一点.

数据参考表:

客户信息表

预期输出

解决方案

要求:针对 lo.Clients 验证 lo.DATA 表中的记录>表并在两个单独的表 lo.Ok 和 lo.Error 中输出 valid 和 invalid 记录.
注意::根据OP，有效记录必须具有 Client OS = 7 .

位于 [B11:F21] 的

lo.DATA 表和位于 [H11:M15]

多个条件:

公式: 

  =((lo.DATA [客户端操作系统] = 7)*(MATCH(lo.DATA [Client Model]，lo.Clients [Model]，0)> 0)*(MATCH(lo.DATA [SKU]，lo.Clients [SKU]，0)> 0)))

尽管AGGREGATE函数返回有效记录，但它错过了记录的位置.要获取位置，请使用

MULTI-CRITERIA公式

最后，使用

建议的解决方案:请确保表中标头的一致性，这是 VLOOKUP 公式返回正确值的关键.

表 lo.OK 中的有效记录，位于 [AL11:AP21]

公式

客户名称: 

  = IFERROR(INDEX(lo.DATA [Client Name]，AGGREGATE(15，6，ROW(AL:AL)/((lo.DATA [客户端操作系统] = 7)*(MATCH(lo.DATA [Client Model]，lo.Clients [Model]，0)> 0)*(MATCH(lo.DATA [SKU]，lo.Clients [SKU]，0)> 0))，ROWS(AL $ 12:AL12)))).)

客户端模型，客户端区域，SKU，客户端操作系统:
在 [AM12] 中输入此公式，然后复制到 [AM12:AP21] 

  = IFERROR(VLOOKUP([@ [Client Name]]，lo.DATA25，MATCH(AM $ 11，lo.DATA [#Headers]，0)，0)，")

表 lo.Error 中的无效记录，位于 [AT11:AY21]

公式

客户名称:
验证是针对 lo.Ok 表执行的(即，如果未找到 lo.DATA 中的记录，则该记录为 invalid 在 lo.Ok 中).

  = IFERROR(INDEX(lo.DATA [Client Name]，AGGREGATE(15，6，ROW(AT:AT)/ISERROR(MATCH(lo.DATA [客户端名称]，Lo.OK [客户端名称]，0))，ROWS(AT $ 12:AT12)))).)

客户端模型，客户端区域，SKU，客户端操作系统:
在 [AU12] 中输入此公式，然后复制到 [AX12:AP21] 

  = IFERROR(VLOOKUP([@ [Client Name]]，lo.DATA，MATCH(AU $ 11，lo.DATA [#Headers]，0)，0)，")

状态: 

  = IF(LEN([@ [客户名称]])= 0，"，SUBSTITUTE(CONCATENATE(错误::"，IF([@ [Client OS]] 7，，OS"，")，IF(ISERROR(MATCH([Client Model]，lo.Clients.A [Model]，0))，，Model"，")，IF(ISERROR(MATCH([SKU]，lo.Clients.A [SKU]，0))，，SKU"，")))，，"，"))

I'm struggling to format a VLOOKUP to work the way I need it to. #N/A's are all over the place.

I'm probably using something entirely incorrectly and not as intended.

I have the following data:

Windows Version Table - AA2:AD3450
Hardware Model Column - P2:P85
Accepted SKU - M2:M55

Client Name - Sheet1 Cell A2:A9000
Client Model - Shee1 Cell J2:J9000
Client Area - Sheet1 Cell B2:B9000
Client OS - Sheet1 Cell E2:E9000

I need to identify the following

  • IF 'Windows Version' resides in 'Client OS' and 'Hardware Model' matches 'Client Model' output 'Client Name' to column AG AND output 'Client Area' to column AH AND output 'Client OS' to Column AI

I hope this makes a little sense. Ive been trying to figure this one out for a while.

Data Ref Table:

Client Information Table

Expected output

解决方案

Requirement: Validate records in lo.DATA table against lo.Clients table and output valid and invalid records in two separated tables lo.Ok and lo.Error.
Note: A valid record must have Client OS= 7 as per OP.

lo.DATA table, located at [B11:F21] and lo.Clients table, located at [H11:M15]

VLOOKUP function: This function has limitations when it comes to the use of criteria, therefore I suggest to use it only for straight matches. We’ll use this function in the tables lo.Ok and lo.Error to return the associated values once the record compliance have been identified.

Criteria:

Client OS:
= lo.DATA[@[Client OS]] = 7 Client OS must be 7 as per OP (change as required).

MATCH function: Use this function to validate record compliance of Model & SKU.
Client Model:
= MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 )

SKU:
= MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 )

Multiple Criteria:

Formula:

= ( ( lo.DATA[Client OS] = 7 )
 * ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
 * ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) )

AGGREGATE function: Use this function to return a list of the valid\invalid records after applying the corresponding multiple criteria.

Use the Array form of the function AGGREGATE(function_num, options, array, [k]), were:

AGGREGATE function parameters:
function_num = 15 (SMALL)
options = 6 (Ignore error values)
array =

( ( lo.DATA[Client OS] = 7 )
 * ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
 * ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) )’

[k] = ROWS([Col$Row:ColRow) (ROWS function ColRow= Address of the 1st cell where the formula is entered, i.e. ROWS(W$12:W12) )

This is the resulting formula:

= AGGREGATE( 15, 6,
 ( ( lo.DATA[Client OS] = 7 )
 * ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
 * ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
 ROWS(W$12:W12) )

Although the AGGREGATE function returns the valid record, it misses the position of the records. To obtain the position use the ROW function

ROW (Col:Col) (i.e. Col= Column where the formula is entered)

This is the resulting formula:

= AGGREGATE( 15, 6,
  ROW(AA:AA) / ( ( lo.DATA[Client OS] = 7 )
 * ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
 * ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
 ROWS(AA$12:AA12) )

MULTI-CRITERIA Formula

Finally, use the INDEX function to return the corresponding record from the `lo.DATA' table.

= IFERROR( INDEX( lo.DATA[Client Name], AGGREGATE( 15, 6,
 ROW(AC:AC) / ( ( lo.DATA[Client OS] = 7 )
 * ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
 * ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
 ROWS(AC$12:AC12) ) ), "" )

Proposed Solution: Please ensure the consistency of the headers among the tables, this is key for the VLOOKUP formulas to return the correct values.

Valid record in table lo.Ok, located at [AL11:AP21]

Formulas

Client Name:

= IFERROR( INDEX( lo.DATA[Client Name], AGGREGATE( 15, 6,
 ROW(AL:AL) / ( ( lo.DATA[Client OS] = 7 )
 * ( MATCH( lo.DATA[Client Model], lo.Clients[Model], 0 ) > 0 )
 * ( MATCH( lo.DATA[SKU], lo.Clients[SKU], 0 ) > 0 ) ),
 ROWS(AL$12:AL12) ) ), "" )

Client Model, Client Area, SKU,Client OS:
Enter this formula in [AM12] then copy to [AM12:AP21]

= IFERROR( VLOOKUP( [@[Client Name]], lo.DATA25,
 MATCH( AM$11, lo.DATA[#Headers], 0 ), 0 ), "" )

Invalid record in table lo.Error, located at [AT11:AY21]

Formulas

Client Name:
The validation is performed against the lo.Ok table, (i.e. a record from lo.DATA is invalid if it is not found in lo.Ok).

= IFERROR( INDEX( lo.DATA[Client Name], AGGREGATE( 15, 6,
 ROW(AT:AT) / ISERROR( MATCH( lo.DATA[Client Name], Lo.OK[Client Name], 0 ) ),
 ROWS(AT$12:AT12) ) ), "" )

Client Model, Client Area, SKU, Client OS:
Enter this formula in [AU12] then copy to [AX12:AP21]

= IFERROR( VLOOKUP( [@[Client Name]], lo.DATA,
 MATCH( AU$11, lo.DATA[#Headers], 0 ), 0 ), "" )

Status:

= IF( LEN([@[Client Name]])=0, "",
 SUBSTITUTE( CONCATENATE( "Error in: ,",
 IF( [@[Client OS]] <> 7, ", OS", "" ),
 IF( ISERROR( MATCH( [Client Model], lo.Clients.A[Model], 0 ) ), ", Model", "" ),
 IF( ISERROR( MATCH( [SKU], lo.Clients.A[SKU], 0 ) ), ", SKU", "" ) ), ",,", "" ) )

这篇关于检查单元格是否符合多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆