Lookup one value in two lists and identify corresponding values

[khawarameer]

Hello All,

I am looking for a formula which will look a value from a two lists and list its corresponding value in result.
Please refer the table below

Lookup Value
Item_1		List1	Lsit2
		Item_1	Item_2
		Item_3	Item_1
		Item_1	Item_3
		Item_2	Item_1
		Item_4	Item_2
		Item_3	Item_4
		Result
		Item_2
		Item_3

<thead>
</thead><tbody>
</tbody>

Please help.

Regards

 

[Peter_SSs]

Re: Lookup one vaule in two lists and identify corresponding values

Here is one way. C12 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down as far as you might ever need.

Excel 2010 32 bit
	A	B	C	D
1	Lookup Value
2	Item_1		List1	Lsit2
3	2		Item_1	Item_2
4			Item_3	Item_1
5			Item_1	Item_3
6			Item_2	Item_1
7			Item_4	Item_2
8			Item_3	Item_4
9
10
11			Result
12			Item_2
13			Item_3
14
List all

Cell Formulas
Range	Formula
A3	=COUNTIF(C3:C8,A2)
C12	{=IF(ROWS(C$12:C12)>A$3,"",INDEX(D$3:D$8,SMALL(IF(C$3:C$8=A$2,ROW(C$3:C$8)-ROW(C$3)+1),ROWS(C$12:C12))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[khawarameer]

Re: Lookup one vaule in two lists and identify corresponding values

Here is one way. C12 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down as far as you might ever need.

Excel 2010 32 bit

	A	B	C	D
1	Lookup Value
2	Item_1		List1	Lsit2
3	2		Item_1	Item_2
4			Item_3	Item_1
5			Item_1	Item_3
6			Item_2	Item_1
7			Item_4	Item_2
8			Item_3	Item_4
9
10
11			Result
12			Item_2
13			Item_3
14

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

List all​

Worksheet Formulas Cell Formula A3 =COUNTIF(C3:C8,A2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C12 {=IF(ROWS(C$12:C12)>A$3,"",INDEX(D$3:D$8,SMALL(IF(C$3:C$8=A$2,ROW(C$3:C$8)-ROW(C$3)+1),ROWS(C$12:C12))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hi Peter_SSs

Thanks for your response. However there i needed the formula that would check both List_1 & List_2 and show the resulting value only once i.e. No repetition.

Can you please help.

Regards

 

[Peter_SSs]

Re: Lookup one vaule in two lists and identify corresponding values

However there i needed the formula that would check both List_1 & List_2 and show the resulting value only once i.e. No repetition.

So that I can better understand, could you then give some sample data and expected results where my formula does not produce those expected results?


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

 

[khawarameer]

Re: Lookup one vaule in two lists and identify corresponding values

Hi Peter_SSs,

Thanks for your response. Your suggestion is noted [Thanks]. Here is some sample data as you suggested, hopefully this will be sufficient.

1	A	B	C
2	Look_Up_Value
3	Khawar
4		List_1	List_2
5		Khawar	Tahami
6		Khawar	Iqbal
7		Khawar	Imran
8		Khawar	Zain
9		Khawar	Tahami
10		Khawar	Iqbal
11		Zain	Khawar
12		Tahami	Khawar
13		Iqbal	Khawar
14		Tahami	Khawar
15		Imran	Khawar
16		Zain	Khawar
17
18		Result	Result Count
19		Tahami	4
20		Iqbal	3
21		Imran	2
22		Zain	3

<thead>
</thead><tbody>
</tbody>

Thanks and regards

 

[Peter_SSs]

Re: Lookup one vaule in two lists and identify corresponding values

Here is some sample data as you suggested, hopefully this will be sufficient.

Here is one possible solution that employs a user-defined function in one column and a standard worksheet formula in another. To implement the udf ..

1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in B18 in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetName(r As Range, lookupname As String, num As Long) As String
  Dim d As Object
  Dim a As Variant
  Dim i As Long, k As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = r.Value
  For i = 1 To UBound(a)
    s = vbNullString
    If a(i, 1) = lookupname Then
      s = a(i, 2)
    ElseIf a(i, 2) = lookupname Then
      s = a(i, 1)
    End If
    If Len(s) > 0 And Not d.exists(s) Then
      d(s) = 1
      k = k + 1
      If k = num Then
        GetName = s
        Exit For
      End If
    End If
  Next i
End Function

Formulas in B18 and C18 copied down.

Excel 2010 32 bit
	A	B	C
1	Look_Up_Value
2	Khawar
3		List_1	List_2
4		Khawar	Tahami
5		Khawar	Iqbal
6		Khawar	Imran
7		Khawar	Zain
8		Khawar	Tahami
9		Khawar	Iqbal
10		Zain	Khawar
11		Tahami	Khawar
12		Iqbal	Khawar
13		Tahami	Khawar
14		Imran	Khawar
15		Zain	Khawar
16
17		Result	Result Count
18		Tahami	4
19		Iqbal	3
20		Imran	2
21		Zain	3
22
List & Count

Cell Formulas
Range	Formula
B18	=GetName(B$4:C$15,$A$2,ROWS(B$18:B18))
C18	=IF(B18="","",COUNTIFS(B$4:B$15,A$2,C$4:C$15,B18)+COUNTIFS(B$4:B$15,B18,C$4:C$15,A$2))

 

[khawarameer]

Re: Lookup one vaule in two lists and identify corresponding values

Hi Peter_SSs,

Your solution worked Sir.

Thanks.

 

[Peter_SSs]

Re: Lookup one vaule in two lists and identify corresponding values

Hi Peter_SSs,

Your solution worked Sir.

Thanks.

You are welcome. Thanks for letting us know.