vlookup - Conditional Vlook up without using VBA

Question

I want to convert an input to desired output. Kindly help. In the output - the columns value should start from most recent (year)

Please click this to see data

Answer 1

Unfortunately VLOOKUP is not able to fulfill that ask. However the INDEX-function can.

Here is a good read on how to use it: http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list

This will work for you spreedsheet, if your input table starts at A1 without a header and your output table starts at H3 with the first ID.

You get this by copy&pasting the first column of your input table to column H and then remove duplicates.

{=IF(ISERROR(INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$H$3,ROW($A$1:$A$7)),ROW(1:1)),3)),"",
INDEX($A$1:$C$7;SMALL(IF($A$1:$A$7=$H$3,ROW($A$1:$A$7)),ROW(1:1)),3))}

---

Let's look at the formula step by step:

The curly brackets tell excel that this is an array formula, the interesting part for you is: when you've inserted the formula (without curly brackets) press shift+ctrl+enter, excel will then know that this is an array formula.

 'error at formula?, then blank, else formula
=IF(ISERROR(....),"",...)

When you autofill this formula you probably dont know how many instances of your lookup variable are. So when you put this formula in 4 cells, but there are only 3 entries, this bit will keep the cell blank instead of giving an error.

---

INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$H$3,ROW($A$1:$A$7)),ROW(1:1)),3))

$A$1:$C$7 is your data matrix. Your IDs (in your case 125 and 501) are to be found in $A$1:$A$7. ROW(1:1) is the absolute(!) rowID, 3 the absolute(!) column id. So when you move your input table those values have to be changed.

What exactly SMALL and INDEX do are well described in the link above. (Or at least better than I could.)

Hope that clarified some parts,

Tom