Complex Vlookup

Copper Contributor

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    
#Topic10051006 
01.07.00.001Ball510 
01.07.00.002Chair820 
01.08.02.001Floor100.5 
01.08.02.002Board501000 
     
     
Table A    
#TopicTypeDescPrice
01.07.00.001Ball1005Material 
01.07.00.001Ball1006U.Source 
01.07.00.002Chair1006U.Source 
01.07.00.002Chair1005Material 
01.08.02.001Floor1005Material 
01.08.02.001Floor1006U.Source 
01.08.02.002Board1006U.Source 
01.08.02.002Board1005Material 
4 Replies

@Isra9632 

=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. 

Thx Quadruple
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

@Isra9632 

Table A    
#TopicTypeDescPrice
01.07.00.001Ball1005Material5
01.07.00.001Ball1006U.Source10
01.07.00.002Chair1006U.Source20
01.07.00.002Chair1005Material8
01.08.02.001Floor1005Material10
01.08.02.001Floor1006U.Source0,5
01.08.02.002Board1006U.Source1000
01.08.02.002Board1005Material50

 

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.

 

 

@Isra9632 

@OliverScheurich'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.