Same Value with Condition

[shbilalcom]

As i m using Sum Values with condition "Sumifs"


but now i want to get the same value with condition

As i attached he picture, i want Column#F to write the Value from Column#A If Column#B have same value as Column#E

For Example Column#F should Find "1" in Column#B and then write the Value from Column#A which is "Ali"

 

[cyrilbrd]

Hi,
do you mean like this?

Excel 2010
	A	B	C	D	E	F
1	value	cdt			val	result
2	1	a			1	a
3	2	b
4	3	c
5	4	d
6	5	e
7	6	f
8	7	g
9	8	h
shbilalcom

a LOOKUP formula will return your query as long as ColumnA (lookup_vector) is sorted in ascending order.

 

[shbilalcom]

i will give you real example, what i need

This is my Sheet one

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Job #[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Ali
[/TD]
[TD]5/4/2014[/TD]
[TD]6[/TD]
[TD]234[/TD]
[/TR]
[TR]
[TD]Ahmad[/TD]
[TD]3/4/2014[/TD]
[TD]8[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]Bilal[/TD]
[TD]2/3/2014[/TD]
[TD]2[/TD]
[TD]874[/TD]
[/TR]
[TR]
[TD]Shafeeq[/TD]
[TD]4/5/2014[/TD]
[TD]5[/TD]
[TD]256[/TD]
[/TR]
[TR]
[TD]Sarfraz[/TD]
[TD]2/5/2014[/TD]
[TD]3[/TD]
[TD]963[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5/2/2014[/TD]
[TD]9[/TD]
[TD]153[/TD]
[/TR]
[TR]
[TD]Ahmad[/TD]
[TD]6/3/2014[/TD]
[TD]1[/TD]
[TD]265[/TD]
[/TR]
[TR]
[TD]Raza[/TD]
[TD]5/6/2014[/TD]
[TD]4[/TD]
[TD]276[/TD]
[/TR]
[TR]
[TD]Rayan[/TD]
[TD]2/7/2014[/TD]
[TD]7[/TD]
[TD]834[/TD]
[/TR]
</tbody>[/TABLE]


now in my 2nd sheet i listed the job number and i want it to take the name of this job number from 1st sheet can any one please make any formula for me.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Job #[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shbilalcom]

Please help me to make kind of Lookup Formula for two sheets

i will give you real example, what i need

This is my Sheet one

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Job #[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Ali
[/TD]
[TD]5/4/2014[/TD]
[TD]6[/TD]
[TD]234[/TD]
[/TR]
[TR]
[TD]Ahmad[/TD]
[TD]3/4/2014[/TD]
[TD]8[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]Bilal[/TD]
[TD]2/3/2014[/TD]
[TD]2[/TD]
[TD]874[/TD]
[/TR]
[TR]
[TD]Shafeeq[/TD]
[TD]4/5/2014[/TD]
[TD]5[/TD]
[TD]256[/TD]
[/TR]
[TR]
[TD]Sarfraz[/TD]
[TD]2/5/2014[/TD]
[TD]3[/TD]
[TD]963[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5/2/2014[/TD]
[TD]9[/TD]
[TD]153[/TD]
[/TR]
[TR]
[TD]Ahmad[/TD]
[TD]6/3/2014[/TD]
[TD]1[/TD]
[TD]265[/TD]
[/TR]
[TR]
[TD]Raza[/TD]
[TD]5/6/2014[/TD]
[TD]4[/TD]
[TD]276[/TD]
[/TR]
[TR]
[TD]Rayan[/TD]
[TD]2/7/2014[/TD]
[TD]7[/TD]
[TD]834[/TD]
[/TR]
</tbody>[/TABLE]


now in my 2nd sheet i listed the job number and i want it to take the name of this job number from 1st sheet can any one please make any formula for me.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Job #[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Re: Please help me to make kind of Lookup Formula for two sheets

Sheet1 consists of A:D.

Sheet2, B2, copied down:

=INDEX(Sheet1!A:A,MATCH(A2,Sheet1!C:C,0))

 

[VoG]

Re: Please help me to make kind of Lookup Formula for two sheets

Try in Sheet2 B2 copied down

=INDEX(Sheet1!A$2:A$10,MATCH(A2,Sheet1!C$2:C$10,0))

 

[vds1]

Re: Please help me to make kind of Lookup Formula for two sheets

In sheet2 B2

=INDEX(Sheet1!A:D,MATCH(A2,Sheet1!C:C,0),1)

where sheet1 A - D is Name1|Date|Job#|Price

and sheet2 A - B is Job#|Name1

 

[cyrilbrd]

Given the following:

Excel 2010
	A	B	C	D
1	Name	Date	Job #	Price
2	Ali	5/4/2014	6	234
3	Ahmad	3/4/2014	8	456
4	Bilal	2/3/2014	2	874
5	Shafeeq	4/5/2014	5	256
6	Sarfraz	2/5/2014	3	963
7	John	5/2/2014	9	153
8	Ahmad	6/3/2014	1	265
9	Raza	5/6/2014	4	276
10	Rayan	2/7/2014	7	834
shbilalcom_1

Excel 2010
	A	B
1	Job #	Name
2	1	Ahmad
3	2	Bilal
4	3	Sarfraz
5	4	Raza
6	5	Shafeeq
7	6	Ali
8	7	Rayan
9	8	Ahmad
10	9	John
shbilalcom_2

Formula in B2 is =INDEX(shbilalcom_1!$A$2:$A$10,MATCH(A2,shbilalcom_1!$C$2:$C$10,0)) copied down till needed.
Note, all JOB numbers are unique.

 

[VoG]

I have merged your two threads.

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).