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?

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_number, table_range, column_index, FALSE) 


   ---

   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_name, table_range, row_index, FALSE) 


   ---

   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_number, table_range, HLOOKUP(customer_name, table_range, row_index, FALSE), 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.

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?