Using IF and VLOOKUP

[camilleso]

Can I have one formula with 2 vlookup search criterias? I have one list of IDs, list one contains CUST, second list does not. If IDs contain CUST, i want the vlookup to search one row of data to match and return values. If no CUST exists, i want the vlookup to search a different row of data to match and return values. Do I have to have 2 separate vlookups or can i combine them somehow?

 

[kweaver]

Could you post some sample data and what you expect as a result(s)?

 

[Sam_D_Ben]

Something similar to this.

Excel 2013/2016
	A	B	C	D	E	F
1	Cust ID	LookUp		Cust ID	Data1	Data1
2	Test1	A		Test1	A
3	Test2	B		Test2	B
4	Test3	D		Test3		D
5	Test4	D		Test4	D
6	Test5	E		Test5	E
Sheet1

Cell Formulas
Range	Formula
B2	=IF(VLOOKUP(A2,$D$2:$F$6,2,0)="",VLOOKUP(A2,$D$2:$F$6,3),VLOOKUP(A2,$D$2:$F$6,2,0))

 

[camilleso]

Sure! My expected result is to add the company type to sheet one which contain a list of ids with and without CUST at the beginning of the numeric value. sheet 2 contain the IDs in separate columns (one with numeric IDs and one that start with CUST); both of which have the company types. Can one formula be made to add company type on sheet one based on the ID match on sheet 2?

Columns on sheet 1
Company Type Account ID
12345
45678
91011
CUST-12345
CUST-45678
CUST-91011

Columns on sheet 2
Customer ID Customer ID (Deprecated) Company Type
12345 Network
45678 Agency
91011 Platform
CUST-12345 Publisher
CUST-45678 SSP
CUST-91011 Network

 

[kweaver]

How's this?

=VLOOKUP(A2,Sheet2!$A$2:$B$7,2,FALSE)

filled down in the B column on Sheet1

Sheet1

<colgroup><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 104, align: right"]12345[/TD]
[TD="class: xl65, width: 64"]Network[/TD]

[TD="class: xl65, align: right"]45678[/TD]
[TD="class: xl65"]Agency[/TD]

[TD="class: xl65, align: right"]91011[/TD]
[TD="class: xl65"]Platform[/TD]

[TD="class: xl65"]CUST-12345[/TD]
[TD="class: xl65"]Publisher[/TD]

[TD="class: xl65"]CUST-45678[/TD]
[TD="class: xl65"]SSP[/TD]

[TD="class: xl65"]CUST-91011[/TD]
[TD="class: xl65"]Network[/TD]

</tbody>

Sheet2

<colgroup><col style="mso-width-source:userset;mso-width-alt:5083;width:104pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> </colgroup><tbody>
[TD="class: xl65, width: 139, align: right"]12345[/TD]
[TD="class: xl65, width: 88"]Network[/TD]

[TD="class: xl65, align: right"]45678[/TD]
[TD="class: xl65"]Agency[/TD]

[TD="class: xl65, align: right"]91011[/TD]
[TD="class: xl65"]Platform[/TD]

[TD="class: xl65"]CUST-12345[/TD]
[TD="class: xl65"]Publisher[/TD]

[TD="class: xl65"]CUST-45678[/TD]
[TD="class: xl65"]SSP[/TD]

[TD="class: xl65"]CUST-91011[/TD]
[TD="class: xl65"]Network[/TD]

</tbody>

 

[camilleso]

Something similar to this.

Excel 2013/2016

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Cust ID[/TD]
[TD="align: center"]LookUp[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cust ID[/TD]
[TD="align: center"]Data1[/TD]
[TD="align: center"]Data1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Test1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Test1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Test2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Test2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Test3[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Test3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Test4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Test4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Test5[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Test5[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]B2[/TH]
[TD="align: left"]=IF(VLOOKUP(A2,$D$2:$F$6,2,0)="",VLOOKUP(A2,$D$2:$F$6,3),VLOOKUP(A2,$D$2:$F$6,2,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sort of. Column A and D have different labels to represent the Cust IDs on sheet 2, although they both have lookup values I need. Sheet one has a combined list of Cust IDs from Column A and D (so A may have Test1 and D may have 1, although it represents the same Cust ID). If the value isn't found in column A, I will need to search D to return the Cust ID.

 

[camilleso]

One edit, to this:
Sort of. Column A and D have different labels to represent the Cust IDs on sheet 2, although they both have lookup values I need. Sheet one has a combined list of Cust IDs from Column A and D (so A may have Test1 and D may have 1, although it represents the same Cust ID). If the value isn't found in column A, I will need to search D to return the Client Type. Client Type is the lookup value I need returned for each Cust ID if it's labeled Test 1 or 1.

 

[Sam_D_Ben]

Something like this.

Excel 2013/2016
	A	B	C	D	E	F	G
1	Cust ID-1	C-Type	Cust ID-2	C-Type		Cust ID	Lookup
2	12345	Network	CUST-12345	Network		12345	Network
3	45678	Agency	CUST-45678	Agency		45678	Agency
4	91011	Platform	CUST-91011	Platform		91011	Platform
5		Publisher		Publisher		CUST-12345	Network
6		SSP		SSP		CUST-45678	Agency
7		Network		Network		CUST-91011	Platform
Sheet1

Cell Formulas
Range	Formula
G2	=IFERROR(VLOOKUP(F2,$A$2:$B$7,2,0),VLOOKUP(F2,$C$2:$D$7,2,0))

 

[camilleso]

Yes, this worked, thanks!!