Forum Discussion
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
=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 nowOliverScheurich'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.
