Vlookup help please

=VLOOKUP(100,a1:b10000,1,TRUE)

try that..

I don't think that will be easy to do. You can use Match and Index to to a left look-up but I don't know how you'd find the closest to 100 rather than exactly 100. Presumably column B can't be sorted ?

thanks starfish that is what i have but it returns n/a

to Ian sometimes the figure doesn't fall exactly on 100 which is the problem

I think there's an easy way to do this, but I don't know it.

Can you do the following though:

In column C, have =abs(100-b1)
Then in column D use rank to return the figure from the vlookup. something like =if(rank c1, c1:c100)=100,vlookup (blah),"irrelevant")

i thought you must have tried that.
Sometimes excel reads the cell formula rather than the value, do you think thats the problem here? does adding a column witb =VALUE(B1) work? it should do but it doesnt always work for me

Do as Lori suggests ..

In Col C insert this formula =ABS(100-B1)

In Col D insert this formula =RANK(C1,$C$1:$C$100,1)

Then use this formula anywhere =INDEX($A$1:$D$100,MATCH(1,$D$1:$D$100,0),1)

Thanks guys for all your help and it is sort of working but I am just getting the column header in cell H4 , instead of the a value

What happens if you delete the column headings row ?

I now get the answer 1 Ian, which is the top cell now

just out of interest, is it likely there will be two values in colB with the same value and are all values returned in colB less than 100?

It could get a bit more complicated if you get 99, 99, 101 etc

are the values in colB always whole numbers or are you getting for example 91.333333333etc?

no jollop there is no duplicate values and the figures do go up to the 100,000s of thousands and yes though there is figures with two decimal places 2300.44 etc

so you could return two values both as close to 100 ie 90 & 110 ? which one would you want to match with column A?

it would then be the higher one Jollop

In C1 (this is an array formula so press control&shift&enter not just enter)

=INDEX($B1:$B1000,MATCH(MIN(ABS($B1:$B$1000-100)),ABS($B$1:$B$1000-100),0),1)

In H8 (or whichever cell you wanted the result in)

=OFFSET($A$1:$B$1000,MATCH($C$1,OFFSET($A$1:$B$1000,0,1,ROWS($A$1:$B$1000),1),0)-1,0,1,1)

Try that it might need adjusting slightly.

no Jollop that ain't working mate, don't worry I will figure something out. Many thanks to you and everyone for your help!!!!

IN H4 enter this =MATCH(H2,dat,0)
Select all data in column B. In the name box type dat then enter
In cell H1 type 100 In the name box type tar then enter
In cell H2 paste this formula and enter it as an array
to do this press shift and control and enter.
=INDEX(dat,MATCH(SMALL(ABS(tar-dat),1),ABS(tar-dat),0))

If you are not familiar with excel
the name box is the left most box in the formula bar
if you select Cell H1 you will see in the name box H1
In that box type as per previous instructions.

When entering an array formula, When you have pasted or typed in the formula,
the insertion must be blinking in the formula bar, then whilst holding down
shift and control press enter. This will put curly brackets around the formula.

Your answer that you wanted will be in H4 as you requested

An interesting aside is that if you had 99 in column B and 101 in
column B. The closest number to 100 is actually 101 in % terms than
what 99 is.
So the formula in H2 would give you 101 even though the difference
in both cases is only 1.