Need help with vlookup nested function

M

mcmilja

Hello,

I need help with creating a vlookup nested function using the following logic:

IF A2=R1 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER01
Interfaces'!$P$3:$Q$5001,2,FALSE)
OR
IF A2=R2 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER02
Interfaces'!$P$3:$Q$5001,2,FALSE)

Any help would be much appreciated!

Thanks,
Jaret
 
P

Pete_UK

You could try this approach:

=IF(A2=R1,VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER01
Interfaces'!$P$3:$Q$5001,2,FALSE),IF(A2=R2,VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER02
Interfaces'!$P$3:$Q$5001,2,FALSE),"neither"))

Will return "neither" if A2 is not equal to R1 or R2. No further error
checking, so you will get #N/A if the item in B3 is not found in the table.

Hope this helps.

Pete
 
D

Don Guillett

modify this to suit
=VLOOKUP(C2,INDIRECT("sheet" & IF(C3=B2,1,2) &"!a9:b11"),2,FALSE)

=VLOOKUP(b3,INDIRECT("'[mtn_ipbh_m40e_capacity_tool.xls]ero" & IF(a2=r1,1,2)
&"!p3:q5001"),2,FALSE)

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
 
P

Pete_UK

Note: in Don's solution, both files will have to be open for INDIRECT to
work.

Pete

Don Guillett said:
modify this to suit
=VLOOKUP(C2,INDIRECT("sheet" & IF(C3=B2,1,2) &"!a9:b11"),2,FALSE)

=VLOOKUP(b3,INDIRECT("'[mtn_ipbh_m40e_capacity_tool.xls]ero" &
IF(a2=r1,1,2) &"!p3:q5001"),2,FALSE)

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
mcmilja said:
Hello,

I need help with creating a vlookup nested function using the following
logic:

IF A2=R1 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER01
Interfaces'!$P$3:$Q$5001,2,FALSE)
OR
IF A2=R2 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER02
Interfaces'!$P$3:$Q$5001,2,FALSE)

Any help would be much appreciated!

Thanks,
Jaret
Click to expand...
Click to expand...
 
D

Don Guillett

Good point
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Pete_UK said:
Note: in Don's solution, both files will have to be open for INDIRECT to
work.

Pete

Don Guillett said:
modify this to suit
=VLOOKUP(C2,INDIRECT("sheet" & IF(C3=B2,1,2) &"!a9:b11"),2,FALSE)

=VLOOKUP(b3,INDIRECT("'[mtn_ipbh_m40e_capacity_tool.xls]ero" &
IF(a2=r1,1,2) &"!p3:q5001"),2,FALSE)

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
mcmilja said:
Hello,

I need help with creating a vlookup nested function using the following
logic:

IF A2=R1 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER01
Interfaces'!$P$3:$Q$5001,2,FALSE)
OR
IF A2=R2 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER02
Interfaces'!$P$3:$Q$5001,2,FALSE)

Any help would be much appreciated!

Thanks,
Jaret
Click to expand...
Click to expand...
Click to expand...
 
M

mcmilja

This worked like a champ! Thank you very much!

Jaret

Pete_UK said:
You could try this approach:

=IF(A2=R1,VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER01
Interfaces'!$P$3:$Q$5001,2,FALSE),IF(A2=R2,VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER02
Interfaces'!$P$3:$Q$5001,2,FALSE),"neither"))

Will return "neither" if A2 is not equal to R1 or R2. No further error
checking, so you will get #N/A if the item in B3 is not found in the table.

Hope this helps.

Pete

mcmilja said:
Hello,

I need help with creating a vlookup nested function using the following
logic:

IF A2=R1 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER01
Interfaces'!$P$3:$Q$5001,2,FALSE)
OR
IF A2=R2 then VLOOKUP(B3,'[MTN_IPBH_M40E_Capacity_Tool.xls]ER02
Interfaces'!$P$3:$Q$5001,2,FALSE)

Any help would be much appreciated!

Thanks,
Jaret
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to combine IF statement and Vlookup function? 2
Can I add a formula or function to Vlookup? 1
Need help with nested funciton using vlookup 6
Newbie needing help with nested VLOOKUP IF function.... 1
Excel - Wich Function should I use? 1
VLOOKUP returning #N/A ?? 3
Need help with If statement 3
Vlookup help 2

Top