Can VLOOKUP (or INDEX) work with LEN for different Cell Formats?

[kibbles]

Hello Everyone,

I have a report that I generate that I use a VLOOKUP function to return an amount from one tab and place it on the first tabe where the project number matches.



Is it possible to do the following:



1. If the two tabs are stored as different formats (one as Text, one as General), can I still get a return value?

The reason I'm asking this is because the table array or the lookup value may have trailing zeros (i.e. 800000.0010 becomes 800000.001) that are lost if I convert to Number and I lose the return value(s) during a VLOOKUP process...



2. If one level is at 2 and the table array has multiple rows of amounts at level 3, can the level 3 rows that match at level 2 become a SUMTOTAL and returned as one (1) value?

For example:

100413.0011.005.03 = 4 levels delineated by the decimal points.



If the first tab has 100413.0011, can it look at the second tab's table and find all of the rows that begin with 100413.0011 (i.e. 100413.0011.006, 100413.0011.3, 100413.0011.006.08) and aggregate to return a single value?



Is this impossible and I"m asking for too much? This would totally save me a LOT of time if it isn't.



I've attached an example.

[protonLeah]

Start with something like:

Please Login or Register  to view this content.

where:
Level =Report!$I$2
StrLen =Report!$J$2:$J$6

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[kibbles]

Start with something like:

Please Login or Register  to view this content.

where:
Level =Report!$I$2
StrLen =Report!$J$2:$J$6

Thank you! I was hoping that the SUMPRODUCT from the Report tab would populate Test tab, but I'm not that great at Excel to use your formula and back into it...

[JeteMc]

Thank You for changing the thread title.
The following formula can be pasted into cell C2 on the Test sheet then copied down and will yield the same results as those in the manual column with few exceptions (I checked a couple and the formula was correct in those cases):

Please Login or Register  to view this content.

Let us know if you have any questions.