LinkBack URL
About LinkBacks
Hi All,
Good day.. hope you are doing well.
Could you please check the attached file where vlookup finds the first value from row, But if i need 2nd row value how it is possible.
Thank You,
Hi All,
Good day.. hope you are doing well.
Could you please check the attached file where vlookup finds the first value from row, But if i need 2nd row value how it is possible.
Thank You,
You could use:
=LOOKUP(2,1/($A$2:$A$5=I9)/(INDEX($B$2:$D$5,,MATCH(J9,$B$1:$D$1,0))<>""),INDEX($B$2:$D$5,,MATCH(J9,$B$1:$D$1,0)))
If you have more than one value per country/city combination, it will return the last one.
Re: How to remove limitation of VLOOKUP
How to remove limitation of VLOOKUP
you can use PowerQuery
Power Query forsee green table
- Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
- Excel 2016 or Excel 365 is built-in, aka Get&Transform
- PowerQuery works with M-language and does not contain any classic formulae or vba code
- What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel
=iferror(indirect(text(aggregate(15,6,(row(rng)*10000+column(rng))/($a$2:$a$5=i9)/($b$1:$d$1=j9)/(rng<>""),countifs($i$9:i9,i9,$j$9:j9,j9)),"r0000c0000"),),"")
Maybe a simpler approach:
=SUMPRODUCT(($A$2:$A$5=I9)*($B$1:$D$1=J9)*($B$2:$D$5<>"")*$B$2:$D$5)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Sandy
Very Impressive.. I want to learn this, But Hows
Very Impressive.. I want to learn this, But HowsOriginally Posted by sandy666
Last edited by AliGW; 07-17-2018 at 06:15 AM.
Thank you..
Try these things
Here's how to do it (Sandy will probably want you to work it out for yourself, but I think Power Query solutions need some explanation for the uninitiated):Hi Sandy
Very Impressive.. I want to learn this, But Hows
1. Select cells A1:D5.
2. On the Data ribbon, click on From Table/Range (fourth icon from the left in the Get & Transform section).
3. In the PQ editor that appears, go to the Transform tab and from the Unpivot Column drop-down, choose Unpivot Other Columns.
4. Go to the Home ribbon and select Close & Load To from the Close & Load drop-down (extreme left). Choose the cell where you want your table to appear.
All done!
Excel 2016 (Windows) 32 bit
Country City 1 City 2 City 3 Country Attribute Value USA USA City 1 USA USA City 2 Canada USA City 3 Canada Canada City 1 Canada City 2 Canada City 3
Sheet: Sheet1
Last edited by AliGW; 07-17-2018 at 06:32 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Neat!
As ever the good old SUMPRODUCT comes to the rescue!
Nice to see it's not gone out of fashion. I suspect, as here, it's used in ways its designers never really imagined.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
@AliGW Thanks Alot ....!!!!!!
Really helpful
Last edited by AliGW; 07-17-2018 at 07:50 AM. Reason: Unnecessary quotation removed.
You're welcome!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks