Formula that shows either name or id from its number.

[Kartiksvn]

number	name	id
1	aaa	1234
2	bbb
3	ccc	1111
4	ddd
5	eee	5678

I want a formula that shows either name or id from its number. If id is empty then shows name like below. I wanna put formula in 'name/id' column.

number	name/id
2	bbb
1	1234
5	5678
4	ddd

Thank you in advance and i really need help on this.

 

[snjpverma]

number​	name​	id​	name/id​
1​	aaa​	1234​	1234​
2​	bbb​	​	bbb​
3​	ccc​	1111​	1111​
4​	ddd​	​	ddd​
5​	eee​	5678​	5678​

​

=IF(C2,C2,B2)

 

[Kartiksvn]

I used vlookup formula in name/id column like this =VLOOKUP(A2,SourceA2:C6,3) but I doesn't have conditional formula which help me to show name if source column id is null than you must show its name.

 

[snjpverma]

=IF(VLOOKUP(A2,Source!$A$1:$C$6,3,0),VLOOKUP(A2,Source!$A$1:$C$6,3,0),VLOOKUP(A2,Source!$A$1:$C$6,2,0))

 

[Kartiksvn]

number​	name​	id​	name/id​
1​	aaa​	1234​	1234​
2​	bbb​	​	bbb​
3​	ccc​	1111​	1111​
4​	ddd​	​	ddd​
5​	eee​	5678​	5678​

​

=IF(C2,C2,B2)

That formula is useful but I have two different sheets and lots off entries with different gapes between each cell of name/id column so I can't copy paste it or scroll down. each and every time I have to input that formula. so can you help me on vlookup formula? which is really helpfull.

 

[snjpverma]

can you paste a sample screenshot? or use a sample data using a XL2BB tool to help us know how exactly your input sheet and your output sheet look like?
below is the screenshot of where you need to click in order to down the XL2BB tool. It's exactly above the space where you are posting the comments now.

 

[Kartiksvn]

=IF(VLOOKUP(A2,Source!$A$1:$C$6,3,0),VLOOKUP(A2,Source!$A$1:$C$6,3,0),VLOOKUP(A2,Source!$A$1:$C$6,2,0))

Thank you so much for this help, But I need little more. This formula is only work for numeric id, and I have some id with special characters or alphabets. Can you help me on that please?

 

[Kartiksvn]

can you paste a sample screenshot? or use a sample data using a XL2BB tool to help us know how exactly your input sheet and your output sheet look like?
below is the screenshot of where you need to click in order to down the XL2BB tool. It's write above the space where you are posting the comments now.



View attachment 44404

 

[Kartiksvn]

can you paste a sample screenshot? or use a sample data using a XL2BB tool to help us know how exactly your input sheet and your output sheet look like?
below is the screenshot of where you need to click in order to down the XL2BB tool. It's exactly above the space where you are posting the comments now.


View attachment 44404

That formula is useful but I have two different sheets and lots off entries with different gapes between each cell of name/id column so I can't copy paste it or scroll down. each and every time I have to input that formula. so can you help me on vlookup formula? which is really helpfull.

SAMPLE 2021.xlsx
	A	B	C
2	AB	NAME (A-B)	BANK NAME
3	1	ALPESH CHAMARADI
4	2	MEHUL CHAMARADI
5	3	MILAN CHAMARADI
6	4	SUNIL CHAUHAN (ASSORT)
7	5	SHAILESHBHAI B. JANI (ASSORT)
8	6	KISHORBHAI JETHAVA (ASSORT)
9	7	JITENDRABHAI JETHAVA (ASSORT)
10	8	GOPAL B. CHAUHAN (ASSORT)
11	9	KETAN CHAUHAN (ASSORT)
12	10	RASIKBHAI G. JASOLIYA (ASSORT)
13	11	PARESHKUMAR B. GOHIL (ASSORT)
14	12	RAJUBHAI G. KADOLIYA (ASSORT)
15	13	RAJUBHAI BARAIYA (ASSORT)
16	14	GOPALBHAI C. CHAUHAN (ASSORT)
17	15	DINESHBHAI V. PARMAR (ASSORT)
18	16	DHARMESH DAVE (ASSORT)
19	17	BHAVESHBHAI M. CHOTALIYA (ASSORT)
20	18	TUSHARBHAI CHAUHAN (MARKER)
21	19	HASAMUKHBHAI D. DABHI (MARKER)
22	20	JITENDRABHAI SHAH (ASSORT)
23	21	DAYABHAI SOLANKI (MARKER)
24	22	RAVIBHAI DIHORA (ASSORT)
A-B

SAMPLE 2021.xlsx
	A	B	C	D	E	F	G
1
2	AB	ALPESH CHAMARADI
3	1	AUGUST*(2021)			શીખાવ ના જમા -
4	# દરેક સ્ટાફ મેમ્બરો નો પગાર વધારો જાન્યુઆરી મહિના ના પગાર વખતે જ થશે.
5	# આથી પગાર વધારા માટે ચાલુ વર્ષ કે દિવાળી પર કોઈએ રજૂઆત કરવી નહિ.
6	# નવા શીખાવ નો બે મહિના નો પગાર જમા રહેશે તથા બે વર્ષ બાદ જ મળશે.
7	BANK NAME		ALPESH CHAMARADI
8	A/C NO.		CASH
9	IFSC CODE		ICIC0006550			બોનસ	0
A-B SLIP

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(A3,'A-B'!$A$3:$P$42,2)
B3	B3	='A-B'!A1
C7	C7	=IF(VLOOKUP(A3,'A-B'!A3:P42,3,0),VLOOKUP(A3,'A-B'!A3:P42,3,0),VLOOKUP(A3,'A-B'!A3:P42,2,0))
C8	C8	=VLOOKUP(A3,'A-B'!$A$3:$P$42,5)
C9	C9	=VLOOKUP(A3,'A-B'!$A$3:$P$42,4)
G9	G9	=VLOOKUP(A3,'A-B'!$A$3:$P$42,15)

 

[Kartiksvn]

consider ID as Bank name. This is my real sheet. I want a formula for C7 cell. and as shown in first sheet I need bank name instead of ID in first example.
thank you for your help.