Log In
Register
Home
New Member
April 18, 2019
Offline
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.
See Answer
VIP
April 21, 2015
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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 |
Answers Post
1 Guest(s)
