desire vlookup to choose next match

  • Thread starter Thread starter click4mrh
  • Start date Start date
C

click4mrh

using the following table:

de sea
wr ted
rb bill
rb ron
qb ed
de dal

vlookup("de",a1.b6,2,false)

Q: this funtion will always return "sea". If "sea" has already bee
chosen and placed in a second table, how do I get it to choose "dal"?

MR
 
Try this array formula:

=INDEX(B1:B6,SMALL(IF(A1:A6=C1,ROW(A1:A6),""),ROW(A1:A6)))

Enter your lookup value (de) in C1

Select D1 to D6.
While the cells are *still* selected, type in the above formula,
Then <Ctrl> <Shift> <Enter>

This will return all corresponding values from column B.
You will get a #NUM! error if there are no more matching values to return.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

using the following table:

de sea
wr ted
rb bill
rb ron
qb ed
de dal

vlookup("de",a1.b6,2,false)

Q: this funtion will always return "sea". If "sea" has already been
chosen and placed in a second table, how do I get it to choose "dal"?

MRH
 
Just another option to play around with .. :

Assuming the data below is in Sheet1,
cols A and B, from row1 down
de sea
wr ted
rb bill
rb ron
qb ed
de dal
Click to expand...
etc

Put in C1:
=IF(COUNTIF($A$1:A1,A1)>1,COUNTIF($A$1:A1,A1),"")
Copy down

This'll put the occurrence numbers for duplicates in col A

In Sheet2
-------------
Assume that listed in cols A and B, from row1 down
are the items (from col A in Sheet1) and
the specified occurrence numbers, viz.:

de 2
rb 2
etc

Put in C1 and array-enter* the formula:

=INDEX(Sheet1!$B$1:$B$12,MATCH(1,(Sheet1!$A$1:$A$12=A1)*(Sheet1!$C$1:$C$12=B
1),0))

*Press CTRL+SHIFT+ENTER (instead of just pressing ENTER)

Copy C1 down

For the sample data given:

C1 will return "dal"
C2 will return "ron"
 
click4mrh said:
using the following table:

de sea
wr ted
rb bill
rb ron
qb ed
de dal

vlookup("de",a1.b6,2,false)

Q: this funtion will always return "sea". If "sea" has already been
chosen and placed in a second table, how do I get it to choose "dal"?

MRH
Click to expand...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

=INDEX(VLOOKUPS("de",A1:B6,2),2)

Alan Beban
 
Hi,

Try this array formula in D1:


=IF(COUNTIF($A$1:$A$100,"de")<ROWS(D$1:D1),"",
INDIRECT("B"&SMALL(IF($A$1:$A$100="de",
ROW($A$1:$A$100),""),ROWS(D$1:D1))))


and fill down.


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--
 
Many thanks to you all, good suggestions for sure, went with Rag's ide
and it worked to a T. Really appreciate your help. MR
 
Back
Top