Hi. Two problems here.
a)
I'm seeking an alternative to the Vlookup function to look for a value in the rightmost column of a table. (Not just move the column to the left.)

b)
I have a table with 8 columns and 20 rows. In the leftmost column I have values 1,2,3….20 down the first column. In the rest of the cell I have different prices (in 7 columns x 20 rows).

I have a price I want to find ( or the nearest price which is higher ) in the table and I have a value in the first column, for example 3. How can I make Excel find it ?

Yours, Kristjan G.

I'd love to help, but I need to clarify something. You say "In the rest of the cell...(in 7 columns). So, are you saying ALL the data entered for one row is all in one cell, or do you have One Column with Values 1, etc and then 7 more columns with one price in each cell?

Soylent Green is PEOPLE!!!

You have a value and want to find a higher price than the original value?

How is the price in the 7 columns associated with the value "3"?

Column A only contains the numbers 1:20 but the rest of the table contains prices.

This target price that you want to use to find the higher price...is it's value in column 1 or somewhere else within the table or somewhere else?

Sorry, but your desciption does not provide sufficient information to offer a solution.

Regards,
Bryan

If I understand your first problem correctly you can solve it with the functions INDEX & MATCH.

If your data in the "lookup table" is in the array E6:G12 and the value you want to search for is at D18 then

=INDEX(E6:G12,MATCH(D18,G6:G12,0),1)

will give you the value of the cell in the first column from the left (ie E)on the row with the corresponding value.

By increasing the 1 to a higher value the column selected can be changed counting from the most left column in the range.

MATCH is determining the # of rows the item is from the top of the array in the selected range G6:G12.

INDEX controls which column is reported on

Cliff

Thank you for quick response and sorry for my English it could be better.

b)
I have a table with 8 columns and 20 rows.
As I said in the leftmost column i.e. column one I have values 1,2,3,… 20 down the column. (The value 1 in row 1, column 1. Value 2 in row 2, column 1 etc.)

Rest of the cells in the table (in 7 columns and 20 rows) contains different prices in each cell.

Now I’m working outside the table or in another sheet. I would like to enter a line number in a cell (like the one which is in the first column) and a price in a another cell, so Excel would take that line number and lookup in the table and find the same price in the table if it is there or the nearest price which is higher.

Yours, Kristjan.