help,.,

P

pierre

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 .
 
P

Prashant Runwal

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 - - -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top