excel - दूसरे को संदर्भित करने के लिए 2 स्तंभों का उपयोग करके Vlookup



excel-formula textjoin (1)

कई तरीकों से:

  1. नंबर रिटर्न से निपटना:

यदि आपके रिटर्न मान संख्या हैं और मैच अद्वितीय है (डेटा में केवल एक जॉन डो है) या आप यदि गुणक हैं तो रिटर्न का योग करना चाहते हैं, तो SUMIFS का उपयोग करना सबसे तेज़ तरीका है।

=SUMIFS(C:C,A:A,J1,B:B,J2)

  1. नॉन न्यूमेरिक रिटर्न के साथ

यदि रिटर्न संख्यात्मक नहीं है या कई गुना हैं तो सूची में पहला मैच पाने के लिए दो तरीके हैं:

ए। एक सहायक स्तंभ:

अगले कॉलम में निम्नलिखित सूत्र लगाएं:

=A1&B1

और सूची को कॉपी करें

फिर INDEX / MATCH का उपयोग करें:

=INDEX(C:C,MATCH(J1&J2,D:D,0))

ख। सरणी सूत्र:

यदि आप आगे कॉलम नहीं बनाना चाहते हैं या नहीं बना सकते हैं तो एक सरणी प्रकार सूत्र का उपयोग करें:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:$A$4)/(($A$1:$A$4=J1)*($B$1:$B$4=J2)),1))

Array प्रकार के फार्मूले डेटा के आकार को डेटा सेट तक सीमित करने की आवश्यकता है।

यदि आपका डेटा सेट नियमित रूप से आकार बदलता है, तो हम डेटा के साथ अंतिम सेल को वापस करने के लिए और अधिक INDEX / MATCH जोड़कर गतिशील होने के लिए उपरोक्त को संशोधित कर सकते हैं:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:INDEX($A:$A,MATCH("ZZZ",A:A)))/(($A$1:INDEX($A:$A,MATCH("ZZZ",A:A))=J1)*($B$1:INDEX($B:$B,MATCH("ZZZ",A:A))=J2)),1))

यह डेटा सेट को बढ़ने या सिकुड़ने की अनुमति देगा और सूत्र केवल उन लोगों के माध्यम से पुनरावृत्ति करेगा जिनके पास डेटा है और पूर्ण स्तंभ नहीं है।

ऊपर वर्णित तरीके बेस्ट-बेटर-गुड के क्रम में सेट किए गए हैं।

  1. एक सेल में कई उत्तर पाने के लिए

यदि आप योग नहीं करना चाहते हैं, या रिटर्न मान पाठ हैं और जॉन डो के कई उदाहरण हैं और आप चाहते हैं कि सभी मान एक सेल में वापस आ जाएँ:

ए। यदि आपके पास Office 365 Excel है, तो आप TEXTJOIN के एक सरणी रूप का उपयोग कर सकते हैं:

=TEXTJOIN(",",TRUE,IF(($A$1:$A$4=J1)*($B$1:$B$4=J2),$C$1:$C$4,""))

एक सरणी सूत्र होने के नाते इसे संपादन मोड से बाहर निकलते समय Enter के बजाय Ctrl-Shift-Enter के साथ पुष्टि करने की आवश्यकता होती है। यदि सही तरीके से किया जाता है तो एक्सेल सूत्र के आसपास {} डाल देगा।

ऊपर AGGREGATE सूत्र की तरह इसे डेटा सेट तक सीमित रखने की आवश्यकता है। पर्वतमाला को INDEX / MATCH कार्यों के साथ ऊपर की तरह गतिशील भी बनाया जा सकता है।

ख। यदि किसी के पास Office 365 Excel नहीं है, तो इस कोड को वर्कबुक से जुड़े मॉड्यूल में जोड़ें:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

फिर ऊपर बताए अनुसार TEXTJOIN () फॉर्मूला का उपयोग करें।

मैं उम्र पाने के लिए पहले तो आखिरी नाम की परिस्थिति में एक वूडअप करने की कोशिश कर रहा हूं। यह कॉलम A के भीतर किया जाएगा, फिर Column B। यदि कॉलम A में पाया जाता है, तो कॉलम B से जारी रखें, यदि Column B में पाया जाता है, तो J3 में आयु डालें जो कि Column C से आती है, और कोई भी "कोई नहीं" डाल सकता है।

यहाँ एक उदाहरण है:

J1 = John
J2 = Doe
J3 = =VLOOKUP J1 & J2,A1:C50,3,FALSE)

जे 3 मेरे पास अभी तक है। क्या मुझे कॉलम A की जाँच करने के लिए Vlookup घोंसला बनाने की आवश्यकता है, फिर कॉलम B को आयु प्राप्त करने के लिए?

यहाँ तालिका सूची का एक उदाहरण दिया गया है:

A     B    C
Jeff  Vel  80
John  Fly  25
Jake  Foo  20
John  Doe  55

J3 = 55।





textjoin