Excel Magic Trick 398: VLOOKUP with Two Lookup Values (IFERROR function also) 2 lookup values

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!

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 !

I am comparing two lists of company names, and am using Vlookups to detect duplicates between the lists. I have figured out the use of wildcards to match smaller length lookup values with longer range values, such as allowing "ABC" to lookup "ABC Media Networks" in the range. I am stuck on how to have the wildcards work the other way, so to speak. What if the lookup value is "Williamsburg Hospital Nursing Department", and a value in the range is "Williamsburg Hospital". Since it is smaller than my lookup, it won't be returned. How can I have Excel detect that there is a value in the range that matches with my lookup value in this way?

With how my spreadsheets are set up, it's sort of a new list vs. a master list idea, so I need to have the vlookups in the new list, with values like "Williamsburg Hospital Nursing Department", so I can sort and research, etc. I'm not new to Excel, but I'm not so great with it yet. I figured out how to achieve what I need to do, but it involves multiple unnecessary vlookups and a lot of awkward sorts.

What I need is a sort of reverse vlookup or a way to put a wildcard on the range values.

Thank you.

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

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Hello there.
First, and before all, I must admit that I am a kind a "dumb" for Excel, because I always were using it, only for better look's of simple tables, schedule's and similar. Although, I was all the time aware of great power's of this program, but newer tested it.
So, finally after few years of tempting, I decided to make a proper "order-list" for the shop in which I work. What I needed is to ,based on products and their prices, make a drop-down box, with list of the products, and after choosing one , proper price will be shown in next column. I find out how to do it with "Excel magic trick #5" on Youtube.
This is what i achieved:

http://i49.tinypic.com/dorzgn.jpg

However,
You see that in row 16, or line 2 in the order box ,we have #N/A below Cena (Price), Iznos PDV (amount of VAT), Cena sa PDV (Amount with VAT), and Iznos (Total amount). If we choose one of the product from drop-down box (now, as You can see none is chosen) and type number in Kolicina(Quantity) column, all this would be automatically changed into proper value.
But what if we have only one thing in order box, instead of 2 or all of 10 ? How to avoid those #N/A, and make instead to be blank cell, which is to be counted as 0, because of final calculation?

In this order-form we have only line 1 and 2 programed to have drop-down box in line Naziv robe (Name of the product). If I putted same drop-down boxes in rest 8 columns, #N/A would be shown in all programed cells bellow, like in line 2...

Kind regards from Serbia

Hello,

I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.

What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.

Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.

BR,

PR

http://www.youtube.com/watch?v=tqCEY5YMyqw

I am trying to duplicate the concept on my spreadsheet. It is conceptually the same, the formula is slightly different.

Mine is as follows:
{=IF(ROWS(A$5:A5)>$B$2,"",
INDEX(US!A$5:A$72,
SMALL(IF(US!$AU$5:$AU$72=Sheet1!$A$2,ROW(US!$AU$5:$AU$72)-ROW(US!$AU$5)+1),ROWS(A$5:A5))))}

Resulting in a #VALUE! error. Can anybody advise me what should the formula me?

Thank a million!
Long