Getting values using VLOOKUP & INDEX and MATCH

[inddon]

Hello There,

I have 2 tables. The first one has rows/columns with common values and the second table which would give the unique results. I am looking out for a way where you can fetch the output using:
1. VLOOKUP
2. INDEX and MATCH

or combination of the above.

Attached is a sample workboook for reference.

Appreciate your support.


Thanks & regards,
Don

 

[Deepak]

I failed to understand the co-relation between table1 & table2.

 

[inddon]

I failed to understand the co-relation between table1 & table2.

Hi Deepak,

Thank you for your response.

I will try to explain it further.

Table1 is the actual data.
Column 'Serial' can have duplicate serials with different names
Column 'Name' is the name for the 'Serial'
Column 'Content' are numbers for the 'Serial'. The first 2 digits start from 10.. and 70.. only

Table2 is the Result.
'Serial', it should list out unique 'Serial' from Table1.
There are 3 more columns:
'Content10', where it should list the Serial's Content starting with 10
'Content70', where it should list the Serial's Content starting with 70
'Name70', if the Serial has a content which starts from 70, then it should list its name in this column

Based on the above, is it possible to get the results shown in the sample workbook(Table2), using VLOOKUP or INDEX and MATCH?

Many thanks

Regards,
Don

 

[bosco_yip]

Try………

1] Table 2, unique "Serial" is in ascending order, H5, array formula copy down :

=IFERROR(INDEX(Table1[Serial],MATCH(0,COUNTIF(Table1[Serial],"<"&Table1[Serial])-SUM(COUNTIF(Table1[Serial],H$4:H4)),0)),"")

Confirm with pressing SHIFT+CTRL+ENTER 3 keys.

2] Table 2, "Content 10", I5 array formula copy down :

=IFERROR(INDEX(Table1[Content],MATCH(1,INDEX((Table1[Serial]=$H5)*(LEFT(Table1[Content],2)=RIGHT(I$4,2)),0),0)),"")

3] Table 2, "Content 70", J5 array formula copy down :

=IFERROR(INDEX(Table1[Content],MATCH(1,INDEX((Table1[Serial]=$H5)*(LEFT(Table1[Content],2)=RIGHT(J$4,2)),0),0)),"")

4] Table 2, "Name 70", K5 array formula copy down :

=IFERROR(INDEX(Table1[Name],MATCH(1,INDEX((Table1[Serial]=$H5)*(LEFT(Table1[Content],2)=RIGHT(K$4,2)),0),0)),"")

Regards
Bosco

 

[inddon]

Try………

1] Table 2, unique "Serial" is in ascending order, H5, array formula copy down :

=IFERROR(INDEX(Table1[Serial],MATCH(0,COUNTIF(Table1[Serial],"<"&Table1[Serial])-SUM(COUNTIF(Table1[Serial],H$4:H4)),0)),"")

Confirm with pressing SHIFT+CTRL+ENTER 3 keys.

2] Table 2, "Content 10", I5 array formula copy down :

=IFERROR(INDEX(Table1[Content],MATCH(1,INDEX((Table1[Serial]=$H5)*(LEFT(Table1[Content],2)=RIGHT(I$4,2)),0),0)),"")

3] Table 2, "Content 70", J5 array formula copy down :

=IFERROR(INDEX(Table1[Content],MATCH(1,INDEX((Table1[Serial]=$H5)*(LEFT(Table1[Content],2)=RIGHT(J$4,2)),0),0)),"")

4] Table 2, "Name 70", K5 array formula copy down :

=IFERROR(INDEX(Table1[Name],MATCH(1,INDEX((Table1[Serial]=$H5)*(LEFT(Table1[Content],2)=RIGHT(K$4,2)),0),0)),"")

Regards
Bosco

Hi Bosco,

Thank you very much for your help. It certainly has taken away most of the VLOOKUP functions I wrote. Using INDEX and MATCH is good.

Learned something new from you.

Regards,
Don

 

[Fjohnson]

Good afternoon!

I am working on a multiple criteria and moving part excel book for chemical recommendations and would like to make a more simple drop down menu sheet for less clutter. Please see the attached file for a better visual but this is my hopeful idea: To be able to drop down and select the chemical/chemicals as well as to drop down and select low/medium/high and get the correct rate from the chemical 'list' sheet.

please if there is a simple way that I am over looking, someone save me!

FJohnson

 

[Nebu]

Hi:

Is this what you are looking for?

Note: Next time start a new thread and do not hijack someone else's thread.

Thanks

 

[bosco_yip]

Good afternoon!

I am working on a multiple criteria and moving part excel book for chemical recommendations and would like to make a more simple drop down menu sheet for less clutter. Please see the attached file for a better visual but this is my hopeful idea: To be able to drop down and select the chemical/chemicals as well as to drop down and select low/medium/high and get the correct rate from the chemical 'list' sheet.

please if there is a simple way that I am over looking, someone save me!

FJohnson

Another option using VLOOKUP function.

1] C6, formula copy down :

=IFERROR(VLOOKUP($B6,tbl_chemicals,MATCH(C$5,tbl_chemicals[#Headers],0),0),"")

2] D6, formula copy down :

=IFERROR(VLOOKUP($B6,tbl_chemicals,7,0),"")

Regards
Bosco

 

[Fjohnson]

Thank you! exactly what I was attempting!