How JustAnswer Works:

  • Ask an Expert
    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
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
A new question is answered every 9 seconds

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).
Thank you for your question, my name is Richard.

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"

Thanks

Customer: replied 3 years ago.

The file ID # XXXXX XXXXX


 

Customer: replied 3 years ago.
Relist: Incomplete answer.
I still haven't received an answer.

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.

Customer: replied 3 years ago.

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.

Customer: replied 3 years ago.

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.

not off the top of my head

you might be able to create a macro to skip a line and insert a row and go down a line and insert a row etc

it might be easier to export the table to something else and import it again

you might export it to a text file and just go down hitting enter down enter down to create new rows that way and then read it back into excel

 

or if you have access you can pull it up as an access table and just add the numbers you want to the end then sort it and read it back

Actually you can do that through excel as well. rather than make spaces in the table

just add the new data to the bottom and then use the sort command under data to resort it into the correct order.

Customer: replied 3 years ago.

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.

Yes you should be able to do an average command for. Any 2 cells and than just copy that formula down

the command would be something like AVG(B1,B2)

then copy that down

but do it in a cell not directly underneath so if you are using the B1,B2 put it in cell A900

 

then when its done do a copy and paste special / data only

into the cell underneath the table

 

then you can just sort that now larger table

flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 8752
Experience: degree in comp sci. 20+ years install work with networks for small and large companies
flopcat98 and 3 other Microsoft Office Specialists are ready to help you