n/a result from a vlookup

[drumfin]

Hi

I have to use a concentrate formula to join to words together to get a unique criteria to look up.

That worked no problem.

I am now in doing my lookup and have tried all of the following in Sheet 1


=VLOOKUP(E2,Week1,5, FALSE)

=VLOOKUP(E2+0,Week1,5, FALSE)

=VLOOKUP(CONCATENATE(C2,B2),Week1,5,FALSE)


Sheet 1 Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row/Column Nunber/Letter
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Field
[/TD]
[TD]Product
[/TD]
[TD]Type
[/TD]
[TD]Product - Type
[/TD]
[TD]Target
[/TD]
[TD]Week 1
[/TD]
[TD]Week 2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]Fruit
[/TD]
[TD]Banana
[/TD]
[TD]Ordered
[/TD]
[TD]=CONCATENATE(C3, " - ",D3)
[/TD]
[TD]100
[/TD]
[TD]=VLOOKUP(E2,Week 1,5,FALSE)
[/TD]
[TD]=VLOOKUP(E2,Week2,5,FALSE)
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]Vegetable
[/TD]
[TD]Cabbage
[/TD]
[TD]Delivered
[/TD]
[TD]=CONCATENATE(C4, " - ",D4)
[/TD]
[TD]200
[/TD]
[TD]=VLOOKUP(E3,Week 1,5, FALSE)
[/TD]
[TD]=VLOOKUP(E3,Week2,5,FALSE)
[/TD]
[/TR]
</tbody>[/TABLE]




WHERE:


*Week1 is a named range in another table

**E2 is a formula, see belowColumn C, D, E, this is text data)

=CONCATENATE(C2, " - ",D2)

***Week1 is B2:F344

***5 is the fifth column on the right of the range (Column F, this is numerical data)

****FALSE, as I need an exact result



Week 1 Sheet Example


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row/Column Number/Letter
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Field
[/TD]
[TD]Product
[/TD]
[TD]Type
[/TD]
[TD]Product - Type
[/TD]
[TD]Week 1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]Fruit
[/TD]
[TD]Banana
[/TD]
[TD]Ordered
[/TD]
[TD]=CONCATENATE(C3, " - ",D3)
[/TD]
[TD]=VLOOKUP(E2,Week1,5,FALSE)
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]Vegetable
[/TD]
[TD]Cabbage
[/TD]
[TD]Delivered
[/TD]
[TD]=CONCATENATE(C4, " - ",D4)
[/TD]
[TD]=VLOOKUP(E2,Week1,5,FALSE)
[/TD]
[/TR]
</tbody>[/TABLE]




I keep on gettting a N/A result


After tying other options then searching forums I tried naming the range, I tried to name the text columns text and the number columns number but nothing has helped.

Any help would be appreciated.

Thanks

 

[starl]

I tested your logic and setup and it worked fine, after a couple of minor tweaks
1. You have Week 1 (two words) in your formula, and Names must be one word
2. Your row 2 formula is concatenating row 3


I just realized tho.. are you wanting to concatenate Product and Type or Field and Product? Because you're concatenating Product and Type (Banana - Ordered)

 

[drumfin]

I tested your logic and setup and it worked fine, after a couple of minor tweaks
1. You have Week 1 (two words) in your formula, and Names must be one word
2. Your row 2 formula is concatenating row 3


I just realized tho.. are you wanting to concatenate Product and Type or Field and Product? Because you're concatenating Product and Type (Banana - Ordered)

Thanks starl

I will try

weekone

instead


Re the other point sorry your right about the row number being wrong thats my typo in re writing the example in here it should be searching row 2 for the column it is

Banana - Ordered

I need

 

[drumfin]

Thanks starl

I will try

weekone

instead


Re the other point sorry your right about the row number being wrong thats my typo in re writing the example in here it should be searching row 2 for the column it is

Banana - Ordered

I need

I just realised I entered the answer in week 1 for the totals also! sorry, I should have copied and pasted

Week 1 - sheet

[TABLE="width: 812"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Field[/TD]
[TD]Product[/TD]
[TD]Type[/TD]
[TD]Product - Type[/TD]
[TD]Week 1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fruit[/TD]
[TD]Banana[/TD]
[TD]Ordered[/TD]
[TD]=CONCATENATE(C2, " - ", D2)[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Vegetable[/TD]
[TD]Cabbage[/TD]
[TD]Delivered[/TD]
[TD]=CONCATENATE(C3, " - ", D3)[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 1 - Sheet

[TABLE="width: 1136"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Field[/TD]
[TD]Product[/TD]
[TD]Type[/TD]
[TD]Product - Type[/TD]
[TD]Target[/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fruit[/TD]
[TD]Banana[/TD]
[TD]Ordered[/TD]
[TD]=CONCATENATE(C2," - ", D2)[/TD]
[TD]100[/TD]
[TD]=VLOOKUP(E2,weekone,5,FALSE)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Vegetables[/TD]
[TD]Cabbage[/TD]
[TD]Delivered[/TD]
[TD]=CONCATENATE(C3," - ", D3)
[/TD]
[TD]200[/TD]
[TD]=VLOOKUP(E3,weekone,5,FALSE)
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


N/A is still showing for me after I renamed the range to letters

 

[starl]

ok.. i'm confused on your layout.
Which sheet is the one being looked into? Which one has the Weekone range name?
Because frankly, they look the same..

If the first table "Week 1 - sheet" is your lookup table and the weekone range name is the ENTIRE table from the ID column to the Week 1 column and you are trying to return the Week 1 column value - then you are using VLOOKUP incorrectly... VLOOKUP matches a value in the leftmost column of the lookuprange. If you are trying to lookup the Product-Type, then your range must start at that column (E). Then the return column would be 2 (not 5).

 

[drumfin]

ok.. i'm confused on your layout.
Which sheet is the one being looked into? Which one has the Weekone range name?
Because frankly, they look the same..

If the first table "Week 1 - sheet" is your lookup table and the weekone range name is the ENTIRE table from the ID column to the Week 1 column and you are trying to return the Week 1 column value - then you are using VLOOKUP incorrectly... VLOOKUP matches a value in the leftmost column of the lookuprange. If you are trying to lookup the Product-Type, then your range must start at that column (E). Then the return column would be 2 (not 5).

ahhhhhhhhhhhhhhhhhhhhhhh

I see now I never copped that, once I change the range from E to F it worked!!

Thank you as I never realised what I was doing wrong.

 

[starl]

ah! ok, so it was a VLOOKUP misunderstanding. To clarify:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value = obviously, your concatenated value. Keep in mind that if it's a number formatted text, the value in the table must also be a number formatted as text
table_array = this is the table you want to match and return from. The lookup_value must be in the left most column of the array. So, if your lookup value is in column C, then your table_array must start with column C (example C2:G100)
col_index_num = this gets a bit confusing, but it's the number of the column from the table_array, not the sheet. So, using the example above (C2:G100), if I want to return a value from column D, then I would put 2 in the formula - because column D is the 2nd column in the table_array.
range_lookup = False for an exact match. True for not. The default is True. I never use True.