Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Doug
 
Posts: n/a
Default lookup combine vlookup with hlookup

I want to search in a table for a specific value. I have a table with item
numbers in column A going down rows 10:200 and the customers names going
across the columns B:K ...

I have a list of sales transactions in anouther worksheet with the quantity
.... I want to lookup the price for an item for a specific customer. So I need
a vlookup to find the row with the appropriate item number (I can do that)
and I need a hlookup to find the correct customer (I can do that) ... the
problem is that I can not seem to do both in one formula. Can anyone help?


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: lookup combine vlookup with hlookup

Yes, you can combine VLOOKUP and HLOOKUP to achieve your goal. Here's how:
  1. In the cell where you want to display the price, start by typing the VLOOKUP formula to find the row with the appropriate item number. The formula should look something like this:

    Formula:
    =VLOOKUP(item_numbertable_rangecolumn_indexFALSE
    Replace "item_number" with the cell reference of the item number you want to look up, "table_range" with the range of your table (including the item numbers in column A), and "column_index" with the number of the column that contains the prices (counting from the left, so if the prices are in column C, column_index would be 3).
  2. Now, instead of typing the column_index number directly, we're going to use the HLOOKUP function to look up the correct column based on the customer name. Replace the column_index argument in your VLOOKUP formula with the following:

    Formula:
    =HLOOKUP(customer_nametable_rangerow_indexFALSE
    Replace "customer_name" with the cell reference of the customer name you want to look up, "table_range" with the same range as before, and "row_index" with the number of the row that contains the prices (counting from the top, so if the prices are in row 9, row_index would be 1).
  3. Now you can combine the two formulas by replacing the column_index argument in your original VLOOKUP formula with the HLOOKUP formula from step 2. The final formula should look something like this:

    Formula:
    =VLOOKUP(item_numbertable_rangeHLOOKUP(customer_nametable_rangerow_indexFALSE), FALSE
    Make sure to replace "item_number" and "customer_name" with the appropriate cell references, and "table_range" and "row_index" with the correct ranges and row numbers as described above.
  4. Press Enter to complete the formula. The cell should now display the price for the specified item and customer.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
JulieD
 
Posts: n/a
Default

Hi Doug

use a combination of INDEX and MATCH instead

=INDEX(A10:K200,MATCH(A1,A10:A200,0),MATCH(B1,A10: K10,0))

where A1 contains the item number that you're looking for
and B1 contains the name of the customer you're interested in

Hope this helps
Cheers
JulieD


"Doug" wrote in message
...
I want to search in a table for a specific value. I have a table with item
numbers in column A going down rows 10:200 and the customers names going
across the columns B:K ...

I have a list of sales transactions in anouther worksheet with the
quantity
... I want to lookup the price for an item for a specific customer. So I
need
a vlookup to find the row with the appropriate item number (I can do that)
and I need a hlookup to find the correct customer (I can do that) ... the
problem is that I can not seem to do both in one formula. Can anyone help?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i get mutiple values using vlookup in excel, lookup value . Abhijeet Excel Discussion (Misc queries) 4 May 19th 05 04:30 AM
How do I lookup and return different values when the lookup value. kg Excel Discussion (Misc queries) 1 January 20th 05 01:53 AM
need check two worksheets to lookup a value Clay Excel Discussion (Misc queries) 2 January 5th 05 09:35 AM
Vlookup Question Jeff Excel Discussion (Misc queries) 2 December 2nd 04 03:40 PM
Need help with modifying VLookUp Tom Excel Discussion (Misc queries) 4 December 2nd 04 01:44 AM


All times are GMT +1. The time now is 04:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"