|
|
Featured Tutorial
Featured Macro
Excel Magic Trick 479: Reverse Two Way Lookup - Robust Formula for Duplicate Situations
See a robust formula that can handle any duplicate situation when doing a Reverse Two Way Lookup. See how to extract column and row headers when the lookup values are data inside the table and there are many duplicate lookup values. This Array Formula uses the functions, IF, OR, COLUMNS, INT, MOD, SMALL, INDEX, COLUMN, ROW, COLUMNS.
This amazing trick comes from DonkeyOte and pgc01 at the Mr Excel Message Board. This video is an improvement on the formula as seen in video Excel Magic Trick #149.5: Reverse 2-Way Lookup w duplicates.
The amazing concept in the video comes from the fact that we have duplicate lookup values and we must return the column and row headers in a specific order. The conceptual trick that will be used is to create a table of numbers in the same shape as the lookup table values and each number will contain both the column number and row number for the lookup value. We will multiply the column numbers by 10^5 then add the row numbers to get a single number that will have information about the column and row index numbers. Absolutely a must see conceptual trick if you want to master Reverse two way lookup!
Reverse Two Way Lookup When Duplicates are Present.
This amazing trick comes from DonkeyOte and pgc01 at the Mr Excel Message Board. This video is an improvement on the formula as seen in video Excel Magic Trick #149.5: Reverse 2-Way Lookup w duplicates.
The amazing concept in the video comes from the fact that we have duplicate lookup values and we must return the column and row headers in a specific order. The conceptual trick that will be used is to create a table of numbers in the same shape as the lookup table values and each number will contain both the column number and row number for the lookup value. We will multiply the column numbers by 10^5 then add the row numbers to get a single number that will have information about the column and row index numbers. Absolutely a must see conceptual trick if you want to master Reverse two way lookup!
Reverse Two Way Lookup When Duplicates are Present.