Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"
Hi, I am using Vlookup, but I have a problem, I have 2 identical items within my range.
If I have as follows:
IC Prov 145.00
IC Prov 150.00
My formula is as follows:
How can I modify the formula to ignore the first "IC Prov" and return the value on the second "IC Prov" without having to change the range?=IF(ISNA(VLOOKUP("IC Prov ",$I$16:$X$83,15,FALSE)),"",VLOOKUP("IC Prov ",$I$16:$X$83,15,FALSE))
Thanks
Last edited by sonar123; 03-05-2011 at 05:53 PM.
A bit messy, but try
=IF(ISNUMBER(MATCH("IC Prov ",INDIRECT("I"&16+MATCH("IC Prov ",I16:I83,0)&":I83"),0)),INDEX($I$16:$X$83,MATCH("IC Prov ",INDIRECT("I"&16+MATCH("IC Prov ",I16:I83,0)&":I83"),0)+MATCH("IC Prov ",I16:I83,0),15),"")
Why are there 3 spaces trailing your Lookup value "IC Prov"?
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
There is space trailing it because it is that way in the ascii format that gets converted into excell, I managed to fix that by using * after the word to subsitute spaces etc. before the last ".
Thanks, will try the formula.
By the way, can you please explain that extra insert?
Thanks
The formula targeted the first IC Prov, how do I get it to return a figure on the second one and not the first one?
Marcol's suggestion should work ok as I see it - returning the appropriate value from Col WOriginally Posted by sonar123
Another alternative would be to adopt a basic SMALL based non-volatile Array:
=IF(COUNTIF($I$16:$I$83,"IC Prov ")<2,"",INDEX($X$16:$X$83,SMALL(IF($I$16:$I$83="IC Prov ",ROW($I$16:$I$83)-ROW($I$16)+1),2))) confirmed with CTRL + SHIFT + ENTER
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hi, thanks, but I have tried it and it still does not target the second IC Prov.
I have attached the spreadsheet.
The total of IC Prov of R33251.94 (cell W34) must be returned in cell E21, not R33251.94 (cell W21)
I know it displays the same figure as provident fund for employee and co should be the same figure. But I have encountered where it is not.
Thanks
Perhaps not the best example given both values cited are the same, however, the Array will work if you modify the INDEX range to W rather than X:
Marcol's original suggestion works without issue also.=IF(COUNTIF($I$16:$I$83,"IC Prov ")<2,"",INDEX($W$16:$W$83,SMALL(IF($I$16:$I$83="IC Prov ",ROW($I$16:$I$83)-ROW($I$16)+1),2))) confirmed with CTRL + SHIFT + ENTER
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks a mill. it works perfectly.
Re: Getting Vlookup to ignore 1st "IC Prov" and return value on second "IC Prov"
Can the formula
be modified so if there is only one IC Prov, it does not return nothing, but returns the first one it sees? And can we eliminate the space trailing IC Prov ?{=IF(COUNTIF($I$1:$I$83,"IC Prov ")<2,"",INDEX($W$1:$W$83,SMALL(IF($I$1:$I$83="IC Prov ",ROW($I$1:$I$83)-ROW($I$1)+1),2)))}
Just using IC Prov as an example. Most other items appears only once and I would like to modify the formula to use for all off them.
Thanks
Perhaps
Regards removing trailing spaces - possibly but that rather depends on the exclusivity of the search terms (use of wildcards in the COUNTIF etc)=IF(COUNTIF($I$1:$I$83,"IC Prov ")=0,"",INDEX($W$1:$W$83,SMALL(IF($I$1:$I$83="IC Prov ",ROW($I$1:$I$83)-ROW($I$1)+1),MIN(2,COUNTIF($I$1:$I$83,"IC Prov "))))) confirmed with CTRL + SHIFT + ENTER
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks once again. You were a great help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks