Question about Microsoft Excel for PC

1 Answer

Need to use the VLOOKUP formula for more then one column

My workbook consists of 2 sheets, a quote and a pricelist. The pricelist onsists of Column A - product, Column B - Price, Column D - product, Column E - Price, Column G - product, Column H - Price. In the quote sheet i've created a VLOOKUP to search within the pricelist sheet, but cannot get the formula to search column A, D and G to find the correct product's price. Please help.

Posted by on

  • nigandy
    nigandy May 11, 2010

    Pls Send the detail of your problem

×

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that gotĀ 5 achievements.

    Governor:

    An expert whose answer gotĀ voted for 20 times.

    Hot-Shot:

    An expert who has answered 20 questions.

  • Expert
  • 53 Answers

You probably need to organise your pricelist sheet so that you only have 2 columns, product and price. If this doesn't fix you, please provide the syntax of your vlookup code.

Posted on Dec 10, 2008

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Excel cell replacement


To have the contents of the cell change from something you input into something else, you would need to put in a macro using VBA. You'll need someone who knows VBA to help with that.

OR... It is a lot easier to get similar results, if you are ok with using a few more cells to do it. The item# will remain where it was entered, and the product name will have to go in a different cell. In that case, you can use a lookup formula.

To do this, lets say your item# is entered in cell 'Sheet1'!C1, and you want the product_name to display in cell 'Sheet1'!D1. On another sheet (lets say sheet2) In cells 'Sheet2'!A1:B5 input the item#'s in the first column (column A1:A5) and input the matching product-names in column B1:B5. This is your lookup data. This sheet can be hidden if you want In cell 'Sheet1'!D1, use a vlookup formula that will look at the item# and find a match in the list, and display the product name for you. =vlookup('Sheet1'!C1,'Sheet2'!$A$1:$B$5,2,false)
This will display N/A# if the number can't be found.

Feb 15, 2013 | Microsoft Excel for PC

1 Answer

Add 20% uplift price to a pricelist in an excel spreedsheet


lets say you have the column B with the prices. Got to column C right beside the first price cell (I'll take B2 as first cell for example) which is C2 and type
=1.2*B2

Copy this formula in the column C all the way down to the last price Cell. That will give you all the updated prices.

Before you delete the column B where all your old prices are you need to do one more thing. Select the entire C column (new prices) go to edit and click on copy. Before you click anywhere else go back to edit and click on paste special and select value. This will change the formula cell you have created a minute ago to a value cell. Now you can delete the old prices. Good luck :)

Feb 25, 2009 | Computers & Internet

4 Answers

How to use vlookup in openoffice using different sheets


=VLOOKUP(A2;Sheet1.$A$3:D27;2;0)

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets
A2 is the cell I want to look up
Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $
2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns
0 is the value to complete the formula

Feb 11, 2009 | Microsoft Excel for PC

2 Answers

LOOK UP AND ALLOCATE


Hi,
Could you write the column names from both the sheets seprately, and one data eg. that you need. It would be easier to understand the problem.


Thanks
Proton

Oct 17, 2008 | Microsoft Excel for PC

1 Answer

Using Vlookup to copy data from another worksheet


=if(isblank(vlookup(Sheet1!A1,Sheet2!$A$1:$B$4,2,0)),"",vlookup(Sheet1!A1,Sheet2!$A$1:$B$4,2,0))

Sep 12, 2008 | Microsoft Excel for PC

1 Answer

Excel formula


I'm assuming you'd like to assign a numerical value to cardinal references (1st, 2nd, 3rd, 4th, etc.).

Best way to do this is to create a quick lookup table on a separate sheet of the same .xls document. Down at the bottom of the page, click on Sheet2 and create a quick table where column A has 1st, 2nd, 3rd, etc. and column B has your values.

Now click back to Sheet1 where your data is and in A2 put this formula:
=VLookup(A1, Sheet2!$A$1:$B$x, 2, FALSE)
Note: replace the lower case x in the formula above with the number of the last row of data in your lookup table on Sheet2.

You can copy and paste this formula down the column to calculate your other values.

Hope that helps!
Terry

Aug 30, 2008 | Microsoft Excel for PC

2 Answers

Unsure of correct formula


You can add a reference from the worksheet 1 to all other worksheets

Is it OK?

Mar 08, 2008 | Microsoft Excel for PC

1 Answer

Lookup,s


If you can move your name column (C) to the first column, you could leverage the VLOOKUP formula pretty easily.
To do this, do the following:
1) Move the C Column to be the A Column, shifting all other columns to the right.
2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)
3) Use A1 as your search field.
4) In A2, enter the following formula:
=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:
$A$1 -> the search field (name your looking for).
$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.
3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.
Also, if you can sort column A (names) it would find results faster, if your data set is large.

Feb 03, 2008 | Microsoft Excel for PC

3 Answers

Function


VLOOKUP is to Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find. The V in VLOOKUP stands for "Vertical." Syntax VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string. Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List. If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. The values in the first column of table_array can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value. Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. Remarks If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value. If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Aug 30, 2007 | Microsoft Office Standard for PC

1 Answer

Vlookup


Hi Ralph, On the first table If the name is in column A and dept is in B. Suppose the new names are in column D here's the formula =vlookup(D1,A:B,2,0) Note a few things - 1. You will only receive the 1st departament. 2. In case that the name in D doesn't appear in A you'll get N/A. This can be solved using the following formula: =if(type(vlookup(D1,A:B,2,0))=16,"",vlookup(D1,A:B,2,0)) Let me know if there's anything else. D.

Aug 27, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Microsoft Excel for PC Logo

209 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2635 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18330 Answers

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...