Lookup and concatenation

[StephenSLR]

I have a concatenated value in Column S =CONCATENATE(P5,Q5,R5,N5)

In column T I have the formula

=LOOKUP(S5,$H$124:$H$2340,$Q$124:$Q$2340)

I can see the concatenated value I want in column A

ELBWSA 234 WPB1.5Sch80_

and this appears below in the H-Q range specified in the lookup formula but in column T there is just a blank.

I suspect the lookup formula is looking for CONCATENATE(P5,Q5,R5,N5) literally.

How do I get it to look up the value instead?

s

 

[Brianwarnock]

It will lookup up the value, suggest you post your spreadsheet.

Brian

 

[qafself]

I suspect that the problem is that the data is not sorted.

You probably need to use VLOOKUP, using the parameter false.

As an aside you do not need to use the concatenate function - you can just put =Value1&value2&value3.....

that said, it's not clear how your data is arranged - suggest you do as Brian asks and post a sample of your data and what you want to achieve.

 

[NBVC]

I have a concatenated value in Column S =CONCATENATE(P5,Q5,R5,N5)

In column T I have the formula

=LOOKUP(S5,$H$124:$H$2340,$Q$124:$Q$2340)

I can see the concatenated value I want in column A

ELBWSA 234 WPB1.5Sch80_

and this appears below in the H-Q range specified in the lookup formula but in column T there is just a blank.

I suspect the lookup formula is looking for CONCATENATE(P5,Q5,R5,N5) literally.

How do I get it to look up the value instead?

s

If that formula returns a blank, then either there is literally a space in column Q where a match is found in column H... or you have some format applied to the cell that makes the result invisible. Check those.. or you may have some sort of macro that is doing something...

 

[StephenSLR]

The file is very large so I've extracted part of it and I've noticed something else happening for the first pivot table - some of the values are correct.

See attached file.

To make it easier I've changed my question to suit.

I have a concatenated value in Column H =CONCATENATE("PIPE",F4,G4,D4)

In column I there is the formula

=LOOKUP(H4,$H$104:$H$152,$Q$104:$Q$152)

I can see the concatenated value I want in column A

PIPESA-106B1Sch80_

and this appears below in the H-Q range specified in the lookup formula but in column I the answer is wrong.

Is there a better method?

s

 

[NBVC]

As indicated by gafself, you will need Vlookup.. to get exact match in an unsorted list...

Try:

=VLOOKUP(H4,$H$104:$Q$152,10,0)

copied down.

 

[StephenSLR]

=VLOOKUP(H4,$H$104:$Q$152,10,0)

Thanks that worked, however there are a lot of N/A 's.

How do I get them to be blank?

Also, so I can learn more, what does the 10 and the 0 in the formula represent?

s

 

[Brianwarnock]

Thanks that worked, however there are a lot of N/A 's.

How do I get them to be blank?

Also, so I can learn more, what does the 10 and the 0 in the formula represent?

s

Lookup Vlookup in help to understand the function, although I think that will use FALSE and TRUE rather than 0 and 1.

Lookup ISERROR on handling #N/A

Brian

 

[StephenSLR]

Thanks all,

It worked a treat.

Another thing bothering me, when I use excel help the font size of the answer is too small - see attached.

Can I increase it to make it easier to read?

s