May 03 2022 11:35 PM - edited May 03 2022 11:39 PM
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 |
May 04 2022 12:56 AM
=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.
May 04 2022 04:29 AM
May 04 2022 04:54 AM
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.
May 04 2022 05:15 AM
@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.