Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask flopcat98 Your Own Question

flopcat98, Consultant

Category: Microsoft Office

Satisfied Customers: 8752

Experience: degree in comp sci. 20+ years install work with networks for small and large companies

23372949

Type Your Microsoft Office Question Here...

flopcat98 is online now

Im using VLOOKUP to reference a bunch of tables I have on

This answer was rated:

★★★★★

I'm using VLOOKUP to reference a bunch of tables I have on several sheets. It's looking up a sounding number, and sending back the corresponding volume to that sounding. It works, but I want to improve precision in the answer. I'd like to know if there's a way to use VLOOKUP where if it doesn't find an exact result (comes up in between two different soundings), instead of giving me the "less than" result it averages the two closest results together (the less than and the greater than).

Can I look at this spreadsheet please? It will be easier.

- Click the following link: http://www.wikisend.com - Upload the file to that website - Once it is uploaded, the resulting page will display a "File ID" number. - Please, give me that "File ID Number"

I don't think that there is any easy way to do that. The vlookup doesn't return any argument that says whether it found an exact match or gave you the nearest one. It also doesn't return the cell number that it found where you could have the formula check if it is equal and give the average if it is not.

You might be able to do it with index match but I would have to play with it for a bit. I haven't downloaded the sheet yet and it would be a complicate formula.

Let me know if you want me to do it because I doubt I would have an answer for you before tomorrow. Otherwise I will opt out.

I've read of it as a possibility using SUMPRODUCT, but I wasn't able to get it to work, I don't think I was understanding the formula enough. Do you know of any other formulas that might work besides INDEX-MATCH?

INDEX MATCH would seem to be the right one if it returns the value of the lowest one when it doesn't match AND tells you that it did not find the exact match. That would seem to be your biggest problem as you don't really know if its matching or not. And then you would have to use the cell reference plus 1 in the row field and might have to use another column to get it correct. I don't know of anything that would just do what you want it to do. It might be possible but it would be a pretty complicated expression.

I am willing to look at it when have some spare time tonight or tomorrow but that's all I can promise. I am happy to opt out and let somebody else do it if you prefer.

Based on what you're telling me, I'm wondering if it would be easier just to alter the original table and make it more precise. Now my question would be if there's an easy way to do that that you can think of, since I have about 10 sheets with 900 rows of numbers. Is there a way I can highlight a column and it'll put an extra number between each of the already-existing numbers? Everything is in ascending order, but inconsistent.

I'd like to avoid adding data individually though. That's about 900 numbers I'd be adding to each of the 10 sheets I have, and 9,000 entries will take me a while. Each sheet has a sounding in feet and a volume in cubic feet side by side for 900 rows. For the soundings, which are relatively consistent, I doubled the amount of soundings from the original data I have by using the "fill" feature and having a start and stop point. So now my soundings go for 1800 rows. I'd like to do something similar with the volumes relative to each of the soundings I expanded but the problem is that the volumes have a curve. They start off with big volume changes in each number and go like that until each volume change is almost non-existent. The last of the volumes have the same number for 5 or 6 soundings. I'd like a way for excel to calculate that curve so I can use the fill and put in the start and stop for each volume side by side with the soundings, and excel will fill in the volumes based on their change between each individual number. Or, if there's a way to have excel get an average number of every two cells in the column, I can add those to the end of the column like you were saying and put them in order.