Trouble with concatenate, vlookup

[mmenashe]

I have a list of values from several drop down boxes on sheet1
sheet 2 has a table with all these possible combinations

So on sheet 2, there is a vlookup function using concatenate

=VLOOKUP("CONCANTENATE(Sheet1!C26, , Sheet1!C25, ,Sheet1!C24)",P10:Q52,2,TRUE)

for whatever reason, it always provides me a value that doesnt match, usually just stays with one value from the vlookup table no matter what the entry is

Is there some kind of formatting I have to do here? Im completely lost in this one

thanks

 

[tweedle]

Try this possibly:
=VLOOKUP(CONCATENATE(Sheet1!C26, Sheet1!C25, Sheet1!C24),P10:Q52,2,TRUE)

Edits:
CONCATENATE spelled incorrectly
removed quotes
removed extra(?) commas

If you want to force the exact match, flip TRUE to FALSE.

 

[mmenashe]

thanks, but the problem is that no matter what, the returned result is always the same value, which is just one of many values in the vlookup table, and its almost never the right one unless its by coincidence

I have the excel file here if anyone wants to take a closer look
http://www.2shared.com/document/1FdLvFh1/mattsexcelproblem.html

The reason I had spaces, was that because instead of the concatenated term being "newredcar" I wanted it to read "new red car" which was also referenced just like that in the following cells

maybe I need to flip it to FALSE and play around some more, still dont know where the problem is though

thanks

Matt

 

[tweedle]

We need to see what these two formulas return:
=CONCATENATE(Sheet3!C26,,Sheet3!C25, ,Sheet3!C24)

=CONCATENATE(Sheet3!C26," ",Sheet3!C25," ",Sheet3!C24)

as well as a sample of Col P data.
(Sorry, I can't get to your link through firewall)

 

[Aladin Akyurek]

thanks, but the problem is that no matter what, the returned result is always the same value, which is just one of many values in the vlookup table, and its almost never the right one unless its by coincidence

I have the excel file here if anyone wants to take a closer look
http://www.2shared.com/document/1FdLvFh1/mattsexcelproblem.html

The reason I had spaces, was that because instead of the concatenated term being "newredcar" I wanted it to read "new red car" which was also referenced just like that in the following cells

maybe I need to flip it to FALSE and play around some more, still dont know where the problem is though

thanks

Matt

Try to post a small, scaled-down sample here. In the meantime, try the following that resembles your intent:

=VLOOKUP(Sheet1!C26&" "&Sheet1!C25&" "&Sheet1!C24,P10:Q52,2,0)

 

[mmenashe]

Try to post a small, scaled-down sample here. In the meantime, try the following that resembles your intent:

=VLOOKUP(Sheet1!C26&" "&Sheet1!C25&" "&Sheet1!C24,P10:Q52,2,0)

woooooooooooo that works

haah man, Ive been stuck on this for such a long time, going to figure out the why/how regarding the ampersand sign now

thanks so much
thanks
thanks

Matt

 

[Aladin Akyurek]

woooooooooooo that works

haah man, Ive been stuck on this for such a long time, going to figure out the why/how regarding the ampersand sign now

thanks so much
thanks
thanks

Matt

Matt,

CONCATENATE would work too, but it means an additional function call and a bit cumbersome. Besides the correct lookup value, you also needed to set the 4th argument in VLOOKUP to 0 (FALSE).