Can't get VLOOKUP to work -- can anyone help?

[Sam577]

Hi,

I am trying to analyse some data with what I *think* should be a Vlookup formula (first time using it), but am hopelessly confused and would be very grateful for some help with the attached, hopefully to amend what I think is an incorrect formula (I've either done it wrong or Vlookup is not suitable to solve this problem).

Column A contains the data I want to be searched
Column B contains the search terms
Columns C and D contain the data I want returned (in columns F and G, which contain the vlookup formulas)

Any help would be very appreciated.

Regards,
Sam

 

[Lilliabeth]

Maybe if you gave a sample answer.

VLOOKUP looks at a value in a cell and then looks in the first column of a table to find the same value there. Columns A and B don't appear to have any matching values. So, give us some sample answers to go by please.

--Lilliabeth

 

[Sam577]

Hi,

I've uploaded a new version so I can illustrate an example.

B5 contains the number 2143 (what I call the lookup value)
A601 contains a match
I would like columns F601 and G601 to display what is in C601 and D601

Hopefully, this makes sense. Thanks for any help.

Sam

 

[SkipVought]

Hi,

Many of us are restricted by company security from access files.

Please post your formula, the related lookup value and a sample of relevant data in the lookup range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sam577]

Hi,

The lookup value "2143" is in cell B5 (the lookup values run from B2 to B514, while what I think is the table array, that is the range that is searched against the lookup value, runs from A2 to A2449)

A601 contains "ISO2143 : 1981"

C601 contains "Paper, PDF"

D601 contains "Colours"

As A601 contains a match on "2143", I would like F601 and G601 (which contain the formulas listed below) to respectively contain "Paper, PDF" and "Colours".

Formula in F601: =VLOOKUP(B601,$A$2:$A$2449,$C:$C, FALSE)
Formula in G601: =VLOOKUP(B601,$A$2:$A$2449,$D:$D, TRUE)

I'm beginning to see my formula must be wrong as it isn't making any sense to me, so any help would be much appreciated.

Many thanks,
Sam

 

[Lilliabeth]

I copied col B to col R.

=VLOOKUP("*"&R5&"*",$A$1:$D$625,3,0)

This will find the first occurance of matching text within text.... if the string "2143" occurs in more than one cell in col A, this will return the value from col C for the first occurence.

--Lilliabeth

 

[Lilliabeth]

oh, and sorry, I didn't take the time to see how many rows down your data went. Probablyshould have said


=VLOOKUP("*"&R5&"*",$A$2:$D$2449,3,0)

--Lilliabeth

 

[Sam577]

Thanks so much Lilliabeth.

Sorry to be dense, but can I just confirm, should I paste that last formula into cell F2 and pull it down to F2449. And how should I amend the formula in G2 (to display any information in column D, "Information", in column G)?

Many thanks,
Sam

 

[Lilliabeth]

Copy the contents of the B column to another column. You should not have your values-to-look-for inside your lookup table. I used column R (I copied all the cells in B to R so that what's in B2 is now also in R2). If you use a different column, then change the forumulas to have that column instead of R.


In F2:

=VLOOKUP("*"&R2&"*",$A$2:$D$2449,3,0) and copy down
(the ColumnNumber argument is a 3, indicating that the formula will return the value from the 3rd col of the table, with is C.)

In G2:

=VLOOKUP("*"&R2&"*",$A$2:$D$2449,4,0) and copy down
(the ColumnNumber argument is a 4, indicating that the formula will return the value from the 4th col of the table, with is D.)

--Lilliabeth

 

[Sam577]

Thank you very much Lilliabeth. That seems to be working much better.

From row 515, because there are no more lookup values in column R from here on, the only thing returned in column F is "PDF, PDF on CD (1)", and column G, "0". Is there anything I can do to prevent this? What I wanted was range R2 to R514 to contain the lookup values, to be searched upon in A2 to A2449, if that makes sense?

Many thanks,
Sam