Problem imbedding Vlookup in IF statement

[smls]

I have attached a copy of what a sample workbook that I am wokring on. I am having trouble getting the nesting right to make the ifs and the vlookups work right.

I am trying to get the vlookup to find the matching codes between sheet 1 and sheet 2. Then i want it to check for a Value B, if that exists I want it to return that value. If it doesn't I want it to look for a Value A and return it if it exists but B does not. Finally if neither exist I want it to return the Value for 160, or $5.

My code is listed as text on Sheet 1 Cell C2

Book1.xlsx

[xenixman]

Not sure what your trying to do, but from a look at your work sheet,
your VLOOKUP() sections are not formed properly,

VLookup( value , table_array , index_number , not_exact_match )

You can not have a Range for the Value,

=if((Vlookup(A:A, <--- not correct

The "Value" is the value to search for in the first column of the "table_array".
You need to specify a single cell.

[smls]

O, and using the A:A does work, in my experience it will give you the value in the A column that coresponds to that row

[smls]

Both ways, the range and the exact cell coordiantes produce the same results for me which are not the results I need to have.

I am looking to return the valueson sheet 2. First the B value should return if it exists, if there is no B value but there is an A value than A should return. If there is no B and no A than the value coresponding to cell B5 or the dollar value $5 should return.

I hope that clears it up.

[ben_hensel]

Well the very first thing I noticed is that your VLOOKUP() is getting an array value instead a single cell as its lookup value.

Let me see if I understand your logic flow:

Take a code (a number) from cell sheet1!A1. Match that to the some cell in Sheet2!A:A

Then, pass the value in the same row in Column C (Value B) if it exists, and Column B (Value A) if it does not. If that doesn't exist, Value A for 160 ($5).

I got something like

C2=IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,3,FALSE)>0, VLOOKUP($A2,Sheet2!$A$2:$C$6,3,FALSE), IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,2,FALSE)>0,VLOOKUP($A2,Sheet2!$A$2:$C$6,2,FALSE),VLOOKUP(160,$A2,Sheet2!$A$2:$C$6,2,FALSE)))

Also for Row B I'm thinking

B2=IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,3,FALSE)>0, "B", IF(VLOOKUP($A2,Sheet2!$A$2:$C$6,2,FALSE)>0,"A"),"A for 160"))

[smls]

Jeffrey,

Thank you, thats exaclty what I needed, the forum comes thorugh again. That is much simpler.

[jeffreybrown]

I see the easiest solution with a helper column...

On Sheet2 D2 copied down

=IF(ISNUMBER(C2),C2,IF(ISNUMBER(B2),B2,5))

Then on Sheet1 C2 copied down

=IFERROR(INDEX(Sheet2!$D$2:$D$6,MATCH(A2,Sheet2!$A$2:$A$6,0)),"")

[smls]

I tried copying your formula into C2 but it is giving me an error

Column B is an actual location, I just put it in to show there were other rows of text data between A and C. It is a system generated location that I won't need to manipulate but thanks for the thought.

[jeffreybrown]

Yes, sometimes simplier is better, and I am simple

You're very welcome...glad you got it all sorted out and thanks for the feedback

[xenixman]

A:A does work,

Yes it does, but it is, as far as I know, an "undocumented feature", and the problem with "undocumented features", is they can vanish without warning.

Here is another solution to your problem, that does not require a helper column:

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$C$6,IF(B2="b",3,2),FALSE)),VLOOKUP(160,Sheet2!$A$2:$C$6,2,FALSE),VLOOKUP(A2,Sheet2!$A$2:$C$6,IF(B2="b",3,2),FALSE))