What is wrong with this vlookup?

[Kazman]

Please help me with this problem. I'm having trouble and I just can't figure it out

Excel 2010 - spreasheet snapshot looks like this:

row.......A..........B........C..........D
12......blank.....blank......1.......N/A#
13......blank.....blank......2.......N/A#
14......J34A........1......blank.....blank
15......blank.....blank...blank.....blank
16......J34B........2......blank.....blank

column B has =IF(A12<>"",MAX($B$11:B11)+1,"") copied down

column C has =IF(ROW()-11>MAX(B:B),"",ROW()-11) copied down

**** column D has =IF(C12="","",VLOOKUP(C12,A:B,1,0)) ****

Why is D12 returning N/A? I want the end list to be
J34A
J34B
blank
blank

*edit* formatting

 

[Expiry]

It's looking for a 1 (in C12) in col A. It doesn't look like there is one, which explains why you're getting the n/a

 

[VoG]

Shouldn't it be something like

=IF(C12="","",INDEX(A1:A100,C12))

 

[scottylad2]

THe Vlookup has to reference the value in the left most column of the table array it's referencing

 

[Kazman]

Wow thank you all for your quick replies. Let me address/clarify a few things. My apologies.

@Expiry - In my example, the value of C12 is "1"

@VoG - that index would look for "1" in column A - that's not quite what I need. I need the value of the cell in column A that corresponds to the row that "1" is found on in column B

@scottylad2 - in other formulas in my workbook, i've used vlookup to reference any column i wish in the dataset

the formula in D12 is:
=IF(C12="","",VLOOKUP(C12,A:B,1,0))

This is what the formula says to me:

check if C12 is blank, if it is, return blank. If it's not blank, then lookup the value of C12 in the dataset comrpised of column A through column B. Return the value of the cell in column 1 from that dataset that corresponds (same row as) where the value of C12 is found in the dataset.

Is my english translation for this formula wrong?

 

[Expiry]

Kazman, the lookup will search for your value - 1 - only in the leftmost column, so in this case it would have to be in column A.

The simplest solution is to swap cols A and B around - if you can do that. Otherwise you need to use a combination of Index() and Match() or similar.

 

[scottylad2]

VLOOKUP(C12,A:B,1,0))

is looking up the value C12, but it would need to find that same value in column A of your AB array for it to return an answer, and going by your syntax the only answer it could return from column 1 of your AB array is the same value thats being looked up.

Index and Match is your best bet, or rearranging your table layout slightly

 

[Kazman]

Kazman, the lookup will search for your value - 1 - only in the leftmost column, so in this case it would have to be in column A.

The simplest solution is to swap cols A and B around - if you can do that. Otherwise you need to use a combination of Index() and Match() or similar.

OMG you are my hero today! Thank you so much. Figured it was something simple :/

I swapped my columns and it works like a champ

Thank you all for your help and your quick replies. I'm sure I'll be back later for more questions.