Vlookup将多个值返回到单元格 [英] Vlookup, return multiple values to a cell

问题描述

有没有从vlookup返回多个值？我想在Sheet 1中的col我将多个值返回到单元格，还是有另一种显示方式（宁可不支持）？

1 ：具有我所有的唯一值（Col F，并在Col I中返回值），

第3页：Col A具有与Col B中唯一的唯一字符串相对应的重复字符串值，包括空白。

编辑

表1或所需结果：

表1：当前

工作表3当前：

当前公式

  = VLOOKUP（F2，Sheet3！A：B，2，FALSE）
  

0，由于与唯一值对应的空白或多个值。

解决方案

在VBA方面，您必须更改该代码有点从我发送给你的链接。这应该工作：

  Option Explicit 
功能vlookupmulti（rngLookup As Variant，rngSource As Range，col As Double）As String 
 Dim d As Double，strCell As String 
 
'如果范围的列少于col 
，则出错如果rngSource.Columns.Count< col然后
 vlookupmulti = CVErr（xlErrNA）
退出函数
结束If 
 
'循环查找列中的行
对于d = rngSource.Row to rngSource.Rows.Count 
如果rngLookup = Sheets（rngSource.Parent.Name）.Cells（d，rngSource.Column）.Value Then 
 strCell = Sheets（rngSource.Parent.Name）.Cells（ d，rngSource.Column + col  -  1）.Value 
如果Len（strCell）> 0然后vlookupmulti = vlookupmulti& strCell& 
 End If 
 Next d 
 
'结尾处删除逗号
如果右（vlookupmulti，2）=，然后
 vlookupmulti =左（vlookupmulti，Len（vlookupmulti） -  2）
结束如果
 
'如果没有结果给出错误
如果vlookupmulti =然后
 vlookupmulti = CVErr（xlErrNA ）
结束如果
 
结束函数
  

is there anyway to return multiple values from a vlookup? I would like col I in Sheet 1 to return multiple values to a cell, or is there another way of displaying this (would rather not pivot)?

Sheet 1 : has all my unique values (Col F, and returning values in Col I),

Sheet 3: Col A has duplicate string values which correspond to unique strings in Col B which are unique, including blanks.

EDIT

Sheet 1 or desired result :

Sheet 1: Current

Sheet 3 Current:

Current formula

=VLOOKUP(F2,Sheet3!A:B,2,FALSE) 

Returns mostly 0's, due to the blanks or multiple values corresponding to the unique values.

解决方案

In terms of VBA then, you have to change the code a bit from what was in the link I sent you. This should work:

Option Explicit
Function vlookupmulti(rngLookup As Variant, rngSource As Range, col As Double) As String
Dim d As Double, strCell As String

'Error if range has less columns than col
If rngSource.Columns.Count < col Then
    vlookupmulti = CVErr(xlErrNA)
    Exit Function
End If

'Loop through rows in the lookup column
For d = rngSource.Row To rngSource.Rows.Count
    If rngLookup = Sheets(rngSource.Parent.Name).Cells(d, rngSource.Column).Value Then
        strCell = Sheets(rngSource.Parent.Name).Cells(d, rngSource.Column + col - 1).Value
        If Len(strCell) > 0 Then vlookupmulti = vlookupmulti & strCell & ", "
    End If
Next d

'Remove comma at end
If Right(vlookupmulti, 2) = ", " Then
    vlookupmulti = Left(vlookupmulti, Len(vlookupmulti) - 2)
End If

'Give error if no results
If vlookupmulti = "" Then
    vlookupmulti = CVErr(xlErrNA)
End If

End Function

这篇关于Vlookup将多个值返回到单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！