help,.,

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pierre - 18 May 2008 07:37 GMT

HELLO
here is the given :

A B
1 asih    234
2 aa    334
3 bb    434
4 asih    534
5 cc    634
6 bb    734
7 asih    834

for repeated items like (bb) and (asih) and in order to get 234, 534, 834
,434 ,734
i use the following formula
{=INDEX(A$1:A$7,SMALL(IF(($A$1:$A$7=TRANSPOSE($J$1:$J$2)),ROW(A$1:A$7)),ROWS(A$1:A1)))}
NOW i would like to use the VLOOKUP function in order to search for (cc)
and (aa) and get their corresponding values
BUT I WOULD LIKE TO COMBINE THE (VLOOKUP) WITH THE =index(.... :
(PRECEDENT FORMULA) IN ORDER TO HAVE THEM BOTH IN ONE SINGLE FORMULA .

Reply to this Message

Prashant Runwal - 18 May 2008 10:25 GMT

Dear Piere there is another alternative for this.... though lengthy it is
worth doing

I have created an additional column before name and a code is created where
it combines sr.no. and name. This is done using formula
=COUNTIF($B2:$B$8,$B2)&B2 in cell a2 and copy this formula till a8.
A    B    C
3asih    asih    234
1aa    aa    134
2bb    bb    434
2asih    asih    534
1cc    cc    634
1bb    bb    734
1asih    asih    834

generate a simple table like this (a10:e13)

    asih    bb    aa    cc
1
2
3

Using formula
=IF(ISERROR((VLOOKUP($A11&B$10,$A$1:$C$8,3,0))),"-",(VLOOKUP($A11&B$10,$A$1:$C$8,3,0))) and copying it from b11 to e13 you get desired result like below

    asih    bb    aa    cc
1    834    734    134    634
2    534    434    -    -
3    234    -    -    -

> HELLO
> here is the given :
[quoted text clipped - 16 lines]
> BUT I WOULD LIKE TO COMBINE THE (VLOOKUP) WITH THE =index(.... :
> (PRECEDENT FORMULA) IN ORDER TO HAVE THEM BOTH IN ONE SINGLE FORMULA .

Reply to this Message