excel if functions with various vloopups

[falcon4]

I'm trying to populate certain cells on one sheet using other sheets as a source. I will select from one drop down list a prefix (M, S, NT) this will be the identification of which vlookup function to execute. Each vlookup will have a different named range. The target item number to look for will be selected from another drop down list.
This drop down list properly changes based on the M, S, or NT selection.
I cannot configure my If statement to execute based on the M,S,NT selection and locate the target value and offset column.
Here is what I have configured.
=IF(AND(B3>0,A3=NT),VLOOKUP(C3,assembly_table,7,FALSE),IF(AND(B3>0,A3=M),VLOOKUP(C3,ITEM_TABLE,2,FALSE),IF(AND(B3>0,A3=S),VLOOKUP(C3,STOCK_TABLE,2,FALSE),"")))

i have created 3 lists named M, NT,and S and did an indirect validation to be able to have the item number drop down lists (cell C3 in above formula). Possibly i may need to rename these tables?

 

[SkipVought]

Hi,

The cell VALUE is a STRING, yes"
[tt]
v v
=IF(AND(B3>0,A3="NT"),VLOOKUP(C3,assembly_table,7,FALSE)
,IF(AND(B3>0,A3="M"),VLOOKUP(C3,ITEM_TABLE,2,FALSE)
,IF(AND(B3>0,A3="S"),VLOOKUP(C3,STOCK_TABLE,2,FALSE),"")))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[falcon4]

i've made the correction and get a #NA error.
evaluating the expression I get to the point where the second vlookup occurs since the selected variable create a true condition. it is this second expression that creates a #NA result. I don't know why the Vlookup doesn't find the target cell value in the defined name. The same look up is usedon other sheets without issue. Any ideas?

 

[SkipVought]

You'll have to debug why that lookup is returning #NA.

Look at the value in column C. Make sure that the EXACT VALUE exists in the first column of the lookup range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

does your named range have the correct number of columns? One of my commonest mistakes is to name the left-hand column where I'm doing the lookup, and forget to include the columns to the right where lookup will find its data to return!

 

[SkipVought]

FYI,

I hardly ever use VLOOKUP.

Rather, I regularly use INDEX & MATCH, since...

1) I Name my ranges by the Column Heading, using Insert > Name > Create -- Create names in Top ROW (of table selection)

2) My lookup value column is seldom the left-most column in the tabel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[falcon4]

Thanks,
What I noticed is that the reference column to look at is a concantenation result. Would that make a difference.
when i debug it, it just goes to a #N/A.
Is there a different way to debug it? I use the show calculation steps to evaluate the formula.

I'm not familiar with the Index and Match functions. Maybe i'll look into those. Can a silmilar IF sequence be used?

 

[SkipVought]

What I noticed is that the reference column to look at is a concantenation result.

What does that mean? Post a concrete example, rather than a description.

Please be complete when you post, including formulas, and relevant values, like...
[tt]
My lookup value in column C is...
M
my formula is...
VLOOKUP(C3,ITEM_TABLE,2,FALSE)
the lookup range, ITEM_TABLE, for the corresponding value is ...
M|ZZZ
and my lookup formula is returning...
yyy
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[falcon4]

understood, thanks,

I've resolved the issue. when i added the quotes around the M, NT, and S earlier on, i also redefined my tables.
Unfortunately i fixed the formula problem using Skips observation and then created another by having the names using the ranges from the wrong sheets.
What an ass...........

Thanks for the help and drawing my attention back to the details.

 

[SkipVought]

Hey, we all make mistakes from time to time. Glad you got it all sorted out.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]