Hi, I want a formula or method that enables me to search one tab for a number between 1 - 25. Where I find it I want to return the value of the cell next to it.

Also there may be more than one instance of this number appearing on this tab, so would I have to have lots of formulas.

Let me know if you need more info

First...feel free to throw in an occasional "Please" and/or "Thank you" when requesting help from the volunteers in this forum. It comes across as a lot more courteous than just saying "I want...". Second, you said "

I want to return the value of the cell next to it".

Returnit in what manner? A Message Box? A specific cell? If there are more than one, where does the second returned value go? And the third one? etc.Third,

between 1 - 25means 1 < x < 25. In other words, 1 and 25 are not included. Is that what you meant?

Hi I appreciate your response.

By 'return' i mean that in the cell next to it there is a some text and I would like this string of text as the output in the final output in the cell with the formula in.

thats exactly what I meant.

Many thanks

I don't think that there is any formula that will search an entire tab for multiple instances of a range of numbers. How would we know how many formulas to have? How would the formula know if it was the first, second, nth occurrence of the value found? As far as I know, a macro is the most efficient way to do this.

Therefore, we're going to need to tell the macro where to put the returned text string.

So, where do you want the text string(s) placed?

Let's make believe you

coulduse a series of formulas. Where would you put these formulas?BTW you didn't answer one of my questions:

Third,

between 1 - 25means 1 < x < 25. In other words, 1 and 25 are not included. Is that what you meant?

I didnt think that was the case. It may be easier if I explained the situation. I have created a calendar in excel like below

1st

42nd243rd 4th etc

16There are 5 cells next to each date and the date is a merged cell of 5 rows.

The numbers represent activities and tasks. e.g. in simple terms 4 = meeting, 5 = holiday, 6 = insurance.

In another workbook I have a list of each of these tasks and then next to them I would like to have the date of each time it appears in the calendar tab.

Yes the numbers are 1-25 no inclusive so start at 2 and end at 24.

Hope this helps?

Many thanks for your quick responses

So if I understand you correctly, you want the output to look like this: Meeting 2nd 4th 18th Insurance 1st 9th 22nd Holiday 2nd 9th 30th

Hi Yes, that is the format that I would like.

Is this possible?Thanks again

Well, as I said, I don't think that there is any set of formula that is going to do that for you. All of the LOOKUP functions are only going to find the first occurance of a given number in a given column or row.

In addition, if the date is in a merged cell next to the value(s), it is still in a specific cell.

Let's say your date is in A1 and merged across A1:A5. Let's say the values for that date are in B1:B4. The date is only "next to" the value in B1. It's offset by 1 row from B2 and offset by 2 rows from B3, etc.

Do you see why that might be difficult to write a formula for this, considering you have an unknown number of instances of 23 different values to look for, but we don't know what Row they are in in relation to any given date?

In additon, once we return a date for a given value, we have to move over 1 column for the next time we want to return a date for that same value.

Worst case is that you have to account for up to 31 occurances of every value, so you would need something like 713 formulas, all of which could find any of the 23 values - and up to 5 different ones - for each date.

I'd like to hear from others to see if they have any ideas, but based on your description, I don't see this being done with formulas.

Assuming a macro is going to be needed, it would be helpful if you provided the ranges for the search, etc.

What columns/rows are your dates in? What columns/rows do we need to search for the values in? etc.

Do you have a copy of workbook that you could provide for testing purposes?

Thank you. How can I upload a document for testing purposes?

If you have a website that you can post a link to, you can put it there. Otherwise you have to email it to someone. If you send me an email address via Private Message, I'll respond with an email address that you can send the workbook to - but it won't be until after 7:00 PM EST.

Here is part of the formula you may be looking for, you will have to figure out how to separate all of the same variances. =IFERROR(VLOOKUP(C5,'tab name'!W:X,2,FALSE),"Not Valid")

This is used for a zip code look up tool. I type a zip code into a box "c5", the zip list is in column W and the names are in column X. If the Zip matches, it will produce a name, if not it shows FALSE.

@extexan35: If the OP's specs are correct, he is running 2003, a version which does not have the IFERROR function available.

I believe that this is the equivalent in 2003.

=IF(ISNA(VLOOKUP(C5,'tab name'!W:X,2,FALSE)),"Not Valid",VLOOKUP(C5,'tab name'!W:X,2,FALSE))

However, I don't think that that formula will meet the OP's objectives.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History