New Contributor

# Complex Vlookup

Hello,

Interest issue it will be great to hear solution for my problem .

2 Tables :

1. One of them with rows, and one of them with columns

2. Need to compare the # and Type columns between the tables and insert the correct # from table B to table A (Insert the column data table  into the row data table

3. For example attached data sheet with the 2 tables .

it will be great to receive a formula for that problem

 Table B # Topic 1005 1006 01.07.00.001 Ball 5 10 01.07.00.002 Chair 8 20 01.08.02.001 Floor 10 0.5 01.08.02.002 Board 50 1000 Table A # Topic Type Desc Price 01.07.00.001 Ball 1005 Material 01.07.00.001 Ball 1006 U.Source 01.07.00.002 Chair 1006 U.Source 01.07.00.002 Chair 1005 Material 01.08.02.001 Floor 1005 Material 01.08.02.001 Floor 1006 U.Source 01.08.02.002 Board 1006 U.Source 01.08.02.002 Board 1005 Material
4 Replies

# Re: Complex Vlookup

``=INDEX(\$C\$4:\$D\$7,MATCH(1,(A12=\$A\$4:\$A\$7)*(B12=\$B\$4:\$B\$7),0),MATCH(C12,\$C\$3:\$D\$3,0))``

Is this what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

# Re: Complex Vlookup

Its still not working
I will try to define my request again :
1. Compare the 2 tables
2. Table A : Type field is by row (and not by column- like table b).
I need to receive a result at the price field by rows , for example :
01.07.00.001 - 1005 : result should be , 5
01.07.00.001-1006 : result should be ,10

etc' - hope it's more clear now

# Re: Complex Vlookup

 Table A # Topic Type Desc Price 01.07.00.001 Ball 1005 Material 5 01.07.00.001 Ball 1006 U.Source 10 01.07.00.002 Chair 1006 U.Source 20 01.07.00.002 Chair 1005 Material 8 01.08.02.001 Floor 1005 Material 10 01.08.02.001 Floor 1006 U.Source 0,5 01.08.02.002 Board 1006 U.Source 1000 01.08.02.002 Board 1005 Material 50

I've highlighted the exemplary results for # and Type red. The formula returns the expected results 5 and 10.

``=INDEX(\$C\$3:\$D\$6,MATCH(1,(A11=\$A\$3:\$A\$6)*(B11=\$B\$3:\$B\$6),0),MATCH(C11,\$C\$2:\$D\$2,0))``

Alternatively you can apply another formula:

``=VLOOKUP(A11&B11,CHOOSE({1,2,3},\$A\$3:\$A\$6&\$B\$3:\$B\$6,\$C\$3:\$C\$6,\$D\$3:\$D\$6),MATCH(C11,\$C\$2:\$D\$2,0)+1,0)``

Enter both formulas with ctrl+shift+enter if you don't work with Office365 or  2021.

# Re: Complex Vlookup

@Quadruple_Pawn's formula is correct, but you have to adjust the ranges and cell references to match your actual layout, of course.

See the attached sample workbook.