Excel Lookup & Search Tip 8 - Vlookup with Lookup Values that Have Extra Values in the Cell in Excel

Hi,

I'm having some trouble with an excel report. To put it as an abstract example, the problem is this:

I have a table with 3 columns and like 90 rows, as below:

A B C
COLUMN1 COLUMN2 COLUMN3
UK ERV 6
DE GHF 13

I want to lookup the values for 'column3' from another worksheet that has a similar table, using the 'column1' and 'column2' values that appear in the same row (in both worksheets).

I tried:

=VLOOKUP(A2&B2,Sheet2!A7:C100,3,FALSE)

but it doesn't work and I have no idea! please help!

thanks alot,
james

hi , i have data stored in excel as column a- Purchase order no column b-
Invoice no i want to query basis purchase order no & result should give
mutiple invoice no stored agst one purchase order no how do i do this using
lookup or something !

Hi All!

I am currently having trouble using a VLOOKUP function. The reason I have a problem is my Lookup values are slightly different to how they appear in the lookup table, but they are similar. They are in fact serial numbers, but the two data export files I am using (annoyingly!) display the codes in slightly different orders. For example, I want to look up a price associate with the code: P 1 AD-N1414May99, but it appears as N1414May99/P 1 AD in the table. They are written in different orders with slashes versus hyphens etc, but I want my function to treat these codes as exact matches. Is there any ideas as to what I can do to achieve this, bearing in mind that I have hundreds of codes which in many cases only differ by one character. Please see below table as an example.

Lookup Values
P 1 AD-N1414May99
P 2 AD-N1414May99
P 3 AD-N1414May99
P 1 AD-N1256May01
P 2 AD-N1256May01


Lookup table
N1414May99/P 1 AD -----$100
N1414May99/P 2 AD -----$120
N1414May99/P 3 AD -----$110
N1256May01/P 1 AE -----$115
N1256May01/P 2 AE -----$110


It's important to realise that I want the codes to be treated as exact matches which is difficult given how similar the codes are to each other. I then want the function to take the price from the lookup table and insert it into the column beside the values.

Thanks a lot for any help!

I am trying to do a vlookup to find xyz.123 in another sheet, but the cell containing xyz.123 actually contains the formula =A1&"."&A2 where A1 is xyz and A2 is 123.

Likewise, using the find tool, excel cannot find xyz.123 in the sheet that contains it, unless you choose Options -> Look in: Values instead of Look in: Formulas.

Is there a way to use Vlookup to find xyz.123 in this situation?

Thanks in advance.

The attached spreadsheet is a sales report (first tab) that we create with multiple customers who have multiple business lines. The second tab contains detaled data that shows payment deductions (charges) for each of those customers, by business line.

The third tab is just a pivot table showing the total charges, by customer, by business line.

I want to be able to lookup by customer number, the charge/deduction for each of the business lines. So I guess in essensce, since the cust# is not unique for each line, the lookup command has to look at the customer number AND the business line, then go over to the Pivot Table and see which charge amount matches the customer number and business line, and then return its value.

I've researched the forums, and am becoming more confused as I go.

Any help is appreciated.

Thanks!
Rick

Is there a way to do a vlookup when looking up against a cell with more than one value in it? For Instance: I want to lookup of the value "1" in A5, my array is A3:A4 and A3 has the values "1,2" in it. I want to return the value in cell A4. So a normal vlookup (=vlookup(A5,A3:A4,2,False)would work if A3 only had "1" in it. Any ideas?

Hello all,

I'm trying to create a function or marco that would allow me to look at a checklist and return on another tab all the values that are marked off.

I.E.

Original List:
Blue X
Green
Yellow X
Red
Orange
Purple X

Next tab:
Blue
Yellow
Purple

Any ideas ? Thanks!

I am trying to do a vlookup based on 4 values.

I have 4 drop down data validations in a row. The first one has the type of material, the second has the height, the third has the width, and the forth a depth. My data is set up in this manner because it's a lot of data and trying to select with more specific names is very long, there are over 700 data combinations. Some data are words and other are numbers.

What I want is for the vlookup to look at all 4 cells and bring back the area that fits all 4 requirements.

If vlookup is not the way to go, that's fine I'm open to suggestions. I've also tied concatenate and A1&A2etc and A1&" "&A2 etc. I get either #N/A or #VALUE in return.

Any suggestions or resources would be greatly appreciated.

Thanks.

I know this has been discussed many times in this forum, but I am having trouble getting vlookup to work with 2 lookup values. I tried using vlookup2, but for one reason or another, it was not working. I have a workbook with 3 sheets. I want to be able to type in a company in column A, a size in column B, and have columns 3, 4, and 5 autofill. However, if the company remains the same but I type in a different size, I need columns 3,4, and 5 to adjust. I hope that makes sense. On the 3rd page I put the table I want the information pulled from. I hope I made sense. Thanks in advance.

Hi,

I need help and advise on how I can reference to two values using vlookup?
I have attached the example.

I need to reference from two lookup values. First is the date, next is the parameter depending if it is 2400, 0600, 1200 or 1800. The matching return value should be from column "swell height /dir"

Example 1:
Date Look up value is 28-Apr-10 and Parameter is 0600H. The return value should be 3.

Example 2:
Date Look up value is 29-Apr-10 and Parameter is 1800H. The return value should be ddd.