Hi!
The situation: One lookup value has several different values in the table array.
The problem: Usualy the vlookup formula shows only the first lookup value from the table array.
The question: How to modify the formula so that all the values from table array are shown, despite the particular lookup value is the same?
See the file with more detailed info attached.Vlookup_modif.xls
Thanks in advance!
Last edited by Arty_1; 12-29-2014 at 09:57 AM.
Regards
Arty
Thanks for the advice, mrice - the criteria of the task you have mentoined is differernt consequently the formula is not that I need - I have only two columns to handle - the lookup value and table array - that's the difference!
I've searched all over the forum and can't find a topic that matches what I'm trying to do. I apologize if this has been covered elsewhere.
Last edited by Arty_1; 12-30-2014 at 01:36 AM.
Hi....
Please see the below response..
As many times we get duplicates lookup values containing different value to pick up..However, in that case Vlookup only picks up the data against the first lookup value from the database...Which is not correct...
Here what we need to do is that to make the lookup value is unique..By doing that we than can easily lookup up actual and real data ..
Please Use the following formula in Cell C1 "=CONCATENATE(A1,"-",COUNTIFS(A1:$A$1,A1))"
RowNo. Lookup value Database New Unique Lookup Value Formulas will be used in Col C
1 A 111 A-1 =CONCATENATE(A2,"-",COUNTIFS(A2:$A$2,A2))
2 B 222 B-1 =CONCATENATE(A3,"-",COUNTIFS(A$2:$A3,A3))
3 C 333 C-1 =CONCATENATE(A4,"-",COUNTIFS(A$2:$A4,A4))
4 D 444 D-1 =CONCATENATE(A5,"-",COUNTIFS(A$2:$A5,A5))
5 D 444-1 D-2 =CONCATENATE(A6,"-",COUNTIFS(A$2:$A6,A6))
6 F 555 F-1 =CONCATENATE(A7,"-",COUNTIFS(A$2:$A7,A7))
7 G 666 G-1 =CONCATENATE(A8,"-",COUNTIFS(A$2:$A8,A8))
In the above function, Countifs will give you the number of time the value is repeated in that particular range. And by merging this count with original lookup value, we will get the Unique lookup value.
Please feel free to get back to me in case of any doubt.
Hi, adhawan06! Let me explain the task one more time:
As you can see there are two similar lookup values FF in column 1, with different values in column 2 60 and 65:
AA 10
BB 20
CC 30
DD 40
EE 50
FF 60
FF 65
GG 70
HH 80
II 90
JJ 100
KK 110
I'd like to find all values of AA, FF, II from column 2, but the problem with vlookup formula is that it gives just a first value from column 2:
AA 10
FF 60
II 90
The expected result would be:
AA 10
FF 60
FF 65
II 90
Your title says "How to modify VLOOKUP.........".
If your situation allows alternatives to VLOOKUP try this array entered formula in C10; fill down and across to D16. Array formulas are committed by holding down Ctrl + Shift and then hitting Enter.Formula:
=IFERROR(INDEX(A$1:A$7,SMALL(IF(ISNA(MATCH($A$1:$A$7,$A$10:$A$15,0)),"",ROW($A$1:$A$7)-MIN(ROW($A$1:$A$7))+1),ROWS($1:1))),"")
The results on your sheet look like this (in red text)
Row\Col
Will this work for you?
Last edited by FlameRetired; 01-11-2015 at 07:20 PM.
FlameRetired, it's just what I was looking for! Thanks a lot!
It's all right with Excel 2007
How to modify the IFERROR formula for Excel 2003?
sorry wrong post
Last edited by paulmacro; 01-14-2015 at 06:00 AM.
I don't have 2003 to test but this shouldFormula:work
=IF($B1="","",INDEX(A$1:A$7,SMALL(IF(ISNA(MATCH($A$1:$A$7,$A$10:$A$15,0)),"",ROW($A$1:$A$7)-MIN(ROW($A$1:$A$7))+1),ROWS($1:1))))
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
It works but 2003rd doesn't recocnize the function, consequently it's not possible to save the file.
Hi
I think some of the 2003 compatibility issue is with the IFERROR function. As far as I can ascertain (google searches) ISNA should work.
I have loaded a file with 2 similar solutions, without IFERROR.
I2:J24 uses the ISNA function to step through blank rows returning a zero, the other formula doesn't have ISNA, it steps past blank rows but returns #NUM! at the end of the data.
Hope this helps.
Last edited by Bobsone; 01-21-2015 at 04:20 AM.
Thanks Bobsone!
I have one more additional question - what will formula look like if I need a values of a few particular letters?
E.g.:
Data base:
A 111
B 222
C 333
D 444
D 444-1
E 555
F 666
F 666-7
Need values for:
D ?
B ?
F ?
Hi Arty,
The Vlookup modification I use is to Vlookup itself until its frequency is achieved then it Vlooksup the next item, row cell by using a LINK to next and so on until the Total Sum is achieved. The data result for each entry is given by using Index & Match. I wrote it on an earlier version of xcl here it is, the solution in green background, input data in blue. 4 additional columns are needed though. I used Arty example #5. I added an extra AA so you can see better how it works. Let me know what you think & if we can fudge together the additional columns somehow.
My best regards
Paul
Hi Arty_1
Am I correct in thinking you want a list of each different column B value that is associated with a specific chosen letter? e.g.
D 444, 444-1
B 222
F 666, 666-7
Hi! Yes you're right!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks