using Vlookup and Hlookup in a spreadsheet 2

[rugbyboy123]

hello everybody,

I have a value (6) in a cell and i want to look it up in a table

1 4
2 11
3 9
4 6
5 3

i want the program to look up 6 and then give the value 4 in another cell

how would one go about this

rugbyboy123

 

[handleman]

Why do you think reading an explanation here is going to be better than reading an explanation from Excel help?

-handleman, CSWP (The new, easy test)

 

[Denial]

Your columns are in the wrong order to use VLOOKUP. (Presumably this is why you are asking the question.)

Use the MATCH function on your second column to find the entry you are seeking. If your first column is always sequential from unity, then that is your answer.

If on the other hand your first column cannot be assumed to be simply the sequential integers, and just looks that way because you entered your example in great haste, then you need to use the result from the above MATCH function as the row reference into an application of the INDEX function to the first column. Try the two operations separately first, and once you understand them individually you can combine them into the one formula.

 

[electricpete]

The attached spreadsheet uses the following formula
=OFFSET(valuerange,MATCH(6,valuerange,0)-1,-1,1,1)
where valuerange refers to the 2nd column of your example


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[rugbyboy123]

thats great thank u very much for your help electricpete

regards

rugbyboy123