vlookup or sum product

[Belinda7237]

I have a work sheet (worksheet 1) that has three columns of data and I want
to match it against another worksheet (worksheet 2) that has those same three
values in one column - if there is a match then i want to return a value in
column J of worksheet 2.

Worksheet 1:

Column D = 00001
Column G = 00000123456
Column H= 000123

Column T = Want value from worksheet 2 column J if all three above match

Worksheet 2:

Column I = 0000100000123456000123
Column J = 98765

 

[Max]

One way ..

In Sheet1,
In T2, normal ENTER:
=INDEX(Sheet2!$J$2:J100,MATCH(TRUE,INDEX(D2&G2&H2=Sheet2!$I$2:$I$100,),0))
Copy down

And if you need an error trap, use ISNA on the MATCH bit of it, indicatively
like this:
=IF(ISNA(MATCH(..)),"",INDEX(..))

aloha? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Belinda7237]

Getting an NA - but i think because my data isnt consistent (sometimes the
00001 is just a 1 in sheet 1 but a 000001 in sheet 2)

What if I just wanted to match column G to the mid of column I (this number
is always consistent)

 

[Max]

Ah, you could tinker around with the earlier expression like this,
normal ENTER to confirm the formula will do (as before):
=INDEX(Sheet2!$J$2:J100,MATCH(TRUE,INDEX(G2=MID(Sheet2!$I$2:$I$100,6,11),),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[tutymano]

Dear evert body

i have 2 work sheet
one woksheet having datas like
aaa 11 12 14 12
bbb 22 22 22 22
aaa 33 23 44 55
aaa 43 23 54 23
bbb 333 333 22 111

another second worksheet is having form
if i type second worksheet a1 cell aaa , i want all data from one worksheet

like result

aaa
11 12 14 12
33 23 44 55
43 23 54 23

Regards
Manoharan

 

[Domenic]

Assuming that Sheet2!A2:E6 contains the source data, and Sheet1!A2
contains 'aaa', try the following...

B2:

=COUNTIF(Sheet2!A2:A6,A2)

C2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(C$2:C2)<=$B$2,INDEX(Sheet2!B$2:B$6,SMALL(IF(Sheet2!$A$2:$A$6=$A$
2,ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1),ROWS(C$2:C2))),"")

However, it would be more efficient to use a helper column...

B2:

=COUNTIF(Sheet2!A2:A6,A2)

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$B$2,SMALL(IF(Sheet2!$A$2:$A$6=$A$2,ROW(Sheet2!$A$2:$A$
6)-ROW(Sheet2!$A$2)+1),ROWS(C$2:C2)),"")

D2, copied across and down:

=IF($C2<>"",INDEX(Sheet2!B$2:B$6,$C2),"")