Hello,
Would you mind helping me put together an index match formula, which populates sheet 1 please?
I would like the formula to retrieve the value from sheet 2 based on the headers as well as the currencies. However, the first match for the example brings back a 0. How can the formula be amended so that I can get the next match, i.e. 5?
Thank you.
I´m afraid your data is not consistent for what you want, seeing your xlsx-file.
In your sheet2 under header 2 you have more than one value for each currency (GBP is 10, later -10, later 10 again and so on).
So when you want on sheet1 some value in a column with header 2, which one should it be?
Frans
It's not impossible, but keep in mind that if you don't find an easy solution, most likely your data structure is not ideal and that usually leads to very complex formulas.
Here is a formula you can use in cell C9:
=INDEX(LookupRange,SUMPRODUCT(SMALL(ROW(LookupRange)*($E$5:$E$24=B9)*(LookupRange<>0),SUMPRODUCT(($E$5:$E$24<>B9)*(LookupRange<>0)+(LookupRange=0))+1))-ROW(LookupRange)+1)
The LookupRange is a defined name, with this formula:
=OFFSET(Sheet1!$E$5:$E$24,0,MATCH(Sheet1!$C$6,Sheet1!$F$4:$H$4,0))
If you use a normal tabular data structure, like the one below, without 0 values, then the formula will be much easier.
| Currency | Attribute | Value |
| GBP | Header 1 | 5.00 |
| SEK | Header 1 | 60.00 |
| GBP | Header 1 | 10.00 |
| PLN | Header 1 | 8.00 |
| GBP | Header 2 | 10.00 |
| USD | Header 2 | -13.11 |
| GBP | Header 2 | -10.00 |
| USD | Header 2 | 13.12 |
| GBP | Header 2 | 4.00 |
| USD | Header 2 | 10.00 |
| GBP | Header 2 | 10.00 |
| USD | Header 2 | 115.00 |
| GBP | Header 2 | 5.00 |
| USD | Header 2 | 8.00 |
| GBP | Header 3 | 45.00 |
| USD | Header 3 | 13.11 |
| GBP | Header 3 | 10.00 |
| USD | Header 3 | -13.12 |
