Excel help please!

[robemcdonald]

I need a little help.

I have a table that I want to extract data from.

I have a value.

I want to look for the number closest to that value in column B and then return the value in column A

So in my example I am looking for 99

Cell B24 is 100

Cell A24 is 35

I want to show 35.


I tried this

=INDEX(B3:B73, MATCH(MIN(ABS(K7)),ABS(A3:A73),0))

but it didn't work.

Any ideas would be most welcome.

Thanks in advance

Rob

[Planet Claire]

VLOOKUP will work, but you'll need to swap around columns A and B first.

[robemcdonald]

Planet Claire said:

VLOOKUP will work, but you'll need to swap around columns A and B first.

does it not have to be an exact match though? I am looking for the closest value and not exact.

[the cueball]

=INDEX(A24:A32,MATCH(MIN(ABS(B24:B32-A6)),ABS(B24:B32-A6),0))

Cell A6 being the number you are trying to find.

[krisdelta]

If you sort the values you want to check against in acending order, I think the "false" selection in clause 4 of vlookup may do this.

[i4got]

robemcdonald said:

I need a little help.

I have a table that I want to extract data from.

I have a value.

I want to look for the number closest to that value in column B and then return the value in column A

So in my example I am looking for 99

Cell B24 is 100

Cell A24 is 35

I want to show 35.


I tried this

=INDEX(B3:B73, MATCH(MIN(ABS(K7)),ABS(A3:A73),0))

but it didn't work.

Any ideas would be most welcome.

Thanks in advance

Rob

Not sure if this is exactly what you're after.




I added a new column A which is ABS($A$1-C4)

$A$1 is my test value (ie 99)

B & C are your columns.

D4 is VLOOKUP(MIN(A4:A13),A4:C13,3,FALSE)

[robemcdonald]

Thanks for the replies I’m going to try them now

[Planet Claire]

i4got said:

Not sure if this is exactly what you're after.




I added a new column A which is ABS($A$1-C4)

$A$1 is my test value (ie 99)

B & C are your columns.

D4 is VLOOKUP(MIN(A4:A13),A4:C13,3,FALSE)

That looks good from how I've understood the question, but I think he wants column 2 (B) returning rather than column 3 (C), so that 100 should say 7.

[i4got]

Planet Claire said:

i4got said:

Not sure if this is exactly what you're after.




I added a new column A which is ABS($A$1-C4)

$A$1 is my test value (ie 99)

B & C are your columns.

D4 is VLOOKUP(MIN(A4:A13),A4:C13,3,FALSE)

That looks good from how I've understood the question, but I think he wants column 2 (B) returning rather than column 3 (C), so that 100 should say 7.

Yes you're right its needs to return column 2.

[robemcdonald]

Still not working.



This is an extract of the actual sheet

In this instance in only want to search column B for the closest match to the value in K4. I then want to return the value from Column A for that row in cell K8

CHeers

Rob

[the cueball]

Unless I’m missing something, the formula I posted works fine???

[eps]

Are you working on the governments Covid spreadsheet???

[i4got]

I've sent you an email.

[bigandclever]

robemcdonald said:

In this instance in only want to search column B for the closest match to the value in K4.

For a start, the value is in K3

[robemcdonald]

Thanks for the help (apart from Big and clever. you can bugger off)
The spreadsheet emailed was a massive help. It wasn't 100% what i wanted, but i was able to modify my data to make it work.

Once again a big thanks for now..

I may have more questions later on.