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