Lookup one value in two lists and identify corresponding values

khawarameer

khawarameer

Board Regular
Joined
Jun 2, 2009
Messages
152
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

[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]Lookup Value
[/TH]
[TH] [/TH]
[TH] [/TH]
[TH] [/TH]
[/TR]
</thead><tbody>[TR]
[TD]Item_1[/TD]
[TD] [/TD]
[TD]List1[/TD]
[TD]Lsit2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_1[/TD]
[TD]Item_2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_3[/TD]
[TD]Item_1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_1[/TD]
[TD]Item_3[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_2[/TD]
[TD]Item_1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_4[/TD]
[TD]Item_2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_3[/TD]
[TD]Item_4[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Result[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Item_3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help.

Regards
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
ABCD
1Lookup Value
2Item_1List1Lsit2
32Item_1Item_2
4Item_3Item_1
5Item_1Item_3
6Item_2Item_1
7Item_4Item_2
8Item_3Item_4
9
10
11Result
12Item_2
13Item_3
14
List all
Cell Formulas
RangeFormula
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.
 
Upvote 0
Re: Lookup one vaule in two lists and identify corresponding values

Peter_SSs said:
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
ABCD
List1Lsit2
Item_1Item_2
Item_3Item_1
Item_1Item_3
Item_2Item_1
Item_4Item_2
Item_3Item_4
Result
Item_2
Item_3

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Lookup Value[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Item_1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
List all​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=COUNTIF(C3:C8,A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]{=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))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Upvote 0
Re: Lookup one vaule in two lists and identify corresponding values

khawarameer said:
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.
Click to expand...
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.
 
Upvote 0
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.

[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]1[/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
</thead><tbody> [TR]
[TD]2[/TD]
[TD]Look_Up_Value[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Khawar[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]List_1[/TD]
[TD]List_2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD]Khawar[/TD]
[TD]Tahami[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD]Khawar[/TD]
[TD]Iqbal[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD]Khawar[/TD]
[TD]Imran[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] [/TD]
[TD]Khawar[/TD]
[TD]Zain[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD]Khawar[/TD]
[TD]Tahami[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] [/TD]
[TD]Khawar[/TD]
[TD]Iqbal[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD] [/TD]
[TD]Zain[/TD]
[TD]Khawar[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD] [/TD]
[TD]Tahami[/TD]
[TD]Khawar[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD] [/TD]
[TD]Iqbal[/TD]
[TD]Khawar[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD]Tahami[/TD]
[TD]Khawar[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD]Imran[/TD]
[TD]Khawar[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD] [/TD]
[TD]Zain[/TD]
[TD]Khawar[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD] [/TD]
[TD]Result[/TD]
[TD]Result Count[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD] [/TD]
[TD]Tahami[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD] [/TD]
[TD]Iqbal[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD] [/TD]
[TD]Imran[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD] [/TD]
[TD]Zain[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


Thanks and regards
 
Last edited:
Upvote 0
Re: Lookup one vaule in two lists and identify corresponding values

khawarameer said:
Here is some sample data as you suggested, hopefully this will be sufficient.
Click to expand...
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)

Rich (BB code): 
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
ABC
1Look_Up_Value
2Khawar
3List_1List_2
4KhawarTahami
5KhawarIqbal
6KhawarImran
7KhawarZain
8KhawarTahami
9KhawarIqbal
10ZainKhawar
11TahamiKhawar
12IqbalKhawar
13TahamiKhawar
14ImranKhawar
15ZainKhawar
16
17ResultResult Count
18Tahami4
19Iqbal3
20Imran2
21Zain3
22
List & Count
Cell Formulas
RangeFormula
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))
 
Upvote 0
Re: Lookup one vaule in two lists and identify corresponding values

Hi Peter_SSs,

Your solution worked Sir.

Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Lookup formula that returns a specific value between two numbers.
Replies
5
Views
334
Joe4
Joe4
H
  • Question Question
Lookup (or Search) for a value in one column across a range of columns, then return the corresponding value for a different column
Replies
6
Views
692
hagia_sofia
hagia_sofia
T
  • Question Question
Lookup within a lookup
2
Replies
10
Views
756
TimeForTim
T
L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
384
Peter_SSs
Peter_SSs
R
  • Question Question
Find J/N Value
Replies
1
Views
172
Fluff
Fluff

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top