VLOOKUP

  • Thread starter Thread starter Rick Stahl
  • Start date Start date
R

Rick Stahl

I have the following formula in a given worsheet called 'Master Fe'

=VLOOKUP(A2,'Re-Arrange'!$2$8000,3,FALSE)

'Re-Arrange' worksheet contains 7 columns and thousands of rows (first
column is lookup value, column 3 contains a value to be returned to 'Master
Fe'. The reference array is sorted correctly and the first proper
(uppermost) value is always returned. A problem exists though when the
lookup value appears more than once (may appear up to 4 times in array).
Since array is sorted, lookup value may exist in 4 consecutive rows, or 3,
or 2, or just 1 row. Whenever more than 1 lookup value exists, only the
first uppermost value is returned, never the second or third or fourth. How
do I advance to the next row to extract next value with the same lookup
value or delete (remove) the row that was already used ? Thanks.

Rick
 
Hi Rick

This is best achieved with the aid of a helper column, let's say Column
A. In there put

=B1&COUNTIF($B$1:$B2,B1)

Then copy down. This will give you entries like;

Cat1
Dog1
Cat2
Rat1
Mouse1
Cat3
Dog2
etc
etc

To then find the 2nd occurence of Cat, use "Cat2" as the Lookup_value
and of course include Column "A" in Lookup_Table

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Sorry, but these solutions do not make sense to me. What I did is added a
column in the lookup array =COUNTIF($A3:$A6,A3) for the number of
occurrences of the 1st column. Basically what I need to do is search for a
given lookup value (A2) in the first column of a this array ('Re-Arrange').
Once it is found, if the value in the second column for this lookup value is
equal to 3, then I would like to return the value in the 4th column. If the
second column value is not 3, continue searching for the next value in the
first column that matches the lookup value and then determine again if the
second column value equals 3. If no such combination exists with the first
and second column, then no value is returned.

Hopefully this is more clearly stated then before.
Rick
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=IF(INDEX(vlookups(A$2,A$13:F$18,2),ROW(A1))=3,INDEX(vlookups(A$2,A$13:F$18,3),ROW(A1)),"")

works on a single sheet with the lookup value in A2 and the lookup array
in A13:F18.

I don't understand 'Re-Arrange'!$2$8000 in your formula. In particular,
I don't understand $2$8000.

Alan Beban
 
Thanks for responding, Alan. Sorry if I made this confusing. I have 2
worksheets: one is Re-Arrange, has 8 columns and is the array that I extract
values from; the other is Master Fe, has about 150 columns, the value in the
first column is my lookup value I use to locate on the array worsheet, and
then various values from the array worsheet are returned to the Master Fe
columns.

So basically the lookup value (from Master Fe column A) is located in array
(Re-Arrange column A) and then if the value in array column B = 3, the value
in array column D is returned to Master Fe. If the value in array column B
is not =3, continue searching for next matching lookup value and perform
same test (column B = 3). If no situation suits, no value is returned.
Thanks !

Rick
 
Hi Rick,

None of this most recent post clarifies what 'Re-Arrange'!$2$8000 refers
to. More specifically, what is the range that the lookup formula is
supposed to be looking in on the Re-Arrange worksheet? Assuming it is
named "dataRange", my formula becomes

=IF(INDEX(VLookups(A$2,dataRange,2),ROW(A1))=3,INDEX(vlookups(A$2,dataRange,3),ROW(A1)),"")

filled down.

Alan Beban
 
Sorry, not sure what I meant with 'Re-Arrange'!$2$8000. This is the
worsheet where the array is located. The correct range is:
'Re-Arrange'!$A$2:$H$21

I tried the following formula:
=IF(INDEX(VLOOKUPS($A2,'Re-Arrange'!$A$2:$H$21,2),ROW(A1))=3,"INDEX(VLOOKUPS
($A16,'Re-Arrange'!$A$2:$H$21,3),ROW(A1))","")

The cell yields a #NAME? error. I understand the IF statement and
VLOOKUP but am not familiar with the INDEX or ROW. So if the lookup value
is found in the first column in any given row in the array Re-Arrange and if
the value in the second column in this same row equals 3, then return the
value in the fourth column in this same row. Otherwise, find the next
occurrence of the lookup value and perform the same test. If 3 is never
found in the second column for this lookup value in the entire array, no
value is returned. Hope this is more clear and I appologize for the
confusion. Thanks, Rick.
 
VLOOKUPS refers to the VLookups function that is included in the freely
downloadable file at http://home.pacbell.net/beban. It must be made
available to your workbook, as described at the website, in order for it
to be used.

The VLookups function returns an array of the values corresponding to
the occurrences of the lookup value. Since ROW(A1) returns 1,
INDEX(VLOOKUPS(Whatever),ROW(A1)) returns the value corresponding to the
1st occurrence of the lookup value. When the formula is copied down,
the ROW(A1) becomes ROW(A2), which returns 2, so the INDEX formula then
refers to the value corresponding to the second occurrence of the lookup
value; and so on.

By the way, I don't know why you inserted the set of quotation marks
around the second INDEX formula, but remove them.

Alan Beban
 
I had already downloaded the file Array Functions from your web site and
saved it to my hard drive. Not sure what else I need to do to make it
available to my woorbook. I also don't know why the quotes were listed,
sorry.

If it makes thing less confusing, this is a typical section of my array
called Re-Arrange.
51K2 1 51K2 10 51 K2 3501 /DK
62K3 2 62K3 12 62 K3 3501 /DK
62K3 1 62K3 11 62 K3 3501 /DK
104K3 4 104K3 16 104 K3 3009 /RD
104K3 3 104K3 15 104 K3 3008 /RD
104K3 2 104K3 8 104 K3 3002 /DK
104K3 1 104K3 7 104 K3 3001 /DK
157K3 1 157K3 9 157 K3 3501 /DK

So if my lookup value from another worksheet is 104K3, I would like the
value 15 returned. Thanks again.
Rick
 
Here's what you might do: Open the Array Functions file, Click on Save
As, Select Microsoft Excel Add-In, save it and close it. Open your
working file, go to the VB Editor, click Tools, References, and check
the saved Add-In file. That will make the Array Functions available to
that working file.

Alan Beban
 
Alan. Me again ! I did as you said, got the file saved and accessable with
my file, etc and with a few minor corrections to thr formular I am making
progress. The formula I used is:

=IF(INDEX(vlookups($A2,'Re-Arrange'!$A$2:$H$21,2),ROW(A1))=3,INDEX(vlookups(
$A2,'Re-Arrange'!$A$2:$H$21,4),ROW(A1)),"")

This formula seems to work fine only if the Formula Argument is TRUE
(Re-Arrange value in the second column is 3). If it is FALSE (Re-Arrange
value in the second column is not 3), no value is returned. And I believe
this is how the formula is writen. But what I would like it to do is
continue to use the same lookup value and search the remainder of the
Re-Arrange array and perform the IF formula again if and when the value is
found. In the array, the same lookup value may be present in up to 4
different consecutive rows, each with a different value in the second column
(values may be 1, 2, 3, or 4) depending on how many times it is present. If
the first value is a 4 in the second row, it should perform the test until a
3 is found or no 3 exists at all for that particular lookup value, not just
return no value.

I thank you for all your help so far ! You made great progress for me. . .
..
Rick
 
You need to fill the formula down the column at least as many cells as
there are occurrences of the lookup value.

Alan Beban
 
The worsheet Re-Arrange containing the array A2:H21 will eventually contain
approximately 8000 rows (A2:H8000). The worksheet with the formula has
about 4000 rows (A2:EP4000). I did a trial run of filling the formula down
about 10 rows and the errors I explained below still exist. Like I
mentioned earlier, each of the 4000 rows contain a lookup value that MAY
EXIST up to 4 times in the Re-Arrange array, but only once per row. In
additon, each of the 4000 rows will retrieve 3 different values from a given
row in the Re-Arrange array if the criteria matches the IF statement.

If I can just get the formula to work when the value in the second column of
the array is not 3, then I can modify it to work for the others. What I
eventually need to do is "left justify" the returned values in particular
columns having the highest number from second column in array placed to the
left. For example, formula in columns CK,CX,DK,DX may return values from
4th column in array, CN,DA,DN,EA may return values from 7th column in array,
and columns CO,DB,DO,EB may return values from 8th column in array. If the
lookup value occurs 4 times in the array, the value in column B=4, then
values from this row in array go in these columns (CK,CN,CO). For the same
lookup value when B=3, these values from array row go in columns CX,DA,DB;
value when B=2 these values from array row go in columns DK,DN,DO; value
when B=1 these values from array row go in columns DX,EA,EB. Likewise if
the lookup value only occurs 3 times then no values will be returned for
DX,EA,EB. If it only occurs 2 times then no values will be returned for
DK,DN,DO and DX,EA,EB, etc. The returned values will be "left justified" in
these columns. Thanks ! Rick.
 
Rick said:
. . . I did a trial run of filling the formula down
about 10 rows and the errors I explained below still exist. . . .
Click to expand...

I don't see any errors explained below.

This is way too complicated for developing a working formula. Set up a
simple set of data that you can easily explain, a la the explanation you
gave when you originally posted. Here is what you said:

"Basically what I need to do is search for a
given lookup value (A2) in the first column of a this array ('Re-Arrange').
Once it is found, if the value in the second column for this lookup value is
equal to 3, then I would like to return the value in the 4th column. If the
second column value is not 3, continue searching for the next value in the
first column that matches the lookup value and then determine again if the
second column value equals 3. If no such combination exists with the first
and second column, then no value is returned."

Stick with that simplistic example until you have a formula that
consistently gives you what you need in that simplistic case. If you
don't yet have that, explain exactly what results you're getting in that
simplistic case that are not what you require. Once you have it worked
out for the simple case, it should be relatively easy to generalize or
otherwise modify the formula for the more complex case you are now sort
of describing.

Alan Beban
 
Back
Top