Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

VLOOKUP help with MS Excel


  • Please log in to reply

#1
qwer

qwer

    Member

  • Member
  • PipPip
  • 13 posts
I am using this formula =VLOOKUP(B4,$A$16:$B$18,2,FALSE ) for VLOOKUP

I have a VLOOKUP table that starts in A16 with $0-$25.$26-$45,$46+ going down and in B16 i have 35%,45%,35% going down. When ever i put it in the formula i get #NA. The value in B4 is $24.00 and i put the formula in cell C5
Please help me thnx

Edited by qwer, 01 August 2006 - 08:18 AM.

  • 0

Advertisements


#2
fructose

fructose

    Retired Staff

  • Retired Staff
  • 284 posts
Ok, there are two problems. The formula is wrong and the table is wrong.

First the table. You need descreet values. $0-$25 isn't a range to Excel, it's a text cell. You need to input descreet vaules. So change it to 0, 26, 46 for A16-A18. You need the smallest value for the range you want to represent for the formula to work.

Next the formula. You were trying to get it to look at the ranges, but VLOOKUP has a built in function that will return the value for a range. It assumes that the table has a range from cell AX to AX+1 and that everything that is greater than or equal to AX and less than AX+1 cooresponds to the value in col_index_num.

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.

So you should make your formula: =VLOOKUP(B4,$A$16:$B$18,2,TRUE)

This is how the formula/table works: It takes the value from B4 ($24) and compares it to the first column in the table from A16 to B18. Since the range_lookup value is TRUE, it sees that 0 is less than 24, so it returns the value in column 2 of the table: 0.35. Setting it to FALSE would give you a #N/A because there is no value of 24. If the value of B4 was 26, you would get a return of 0.45 since the value in A17 is less than or equal the lookup_value.

Just format the cells to display percentage and you'll get the display you want.

Edited by fructose, 01 August 2006 - 01:30 PM.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP