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

12345	Network
45678	Agency
91011	Platform
CUST-12345	Publisher
CUST-45678	SSP
CUST-91011	Network

<colgroup><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

Sheet2

12345	Network
45678	Agency
91011	Platform
CUST-12345	Publisher
CUST-45678	SSP
CUST-91011	Network

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

 

[camilleso]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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