Search in a row for a column number after a Vlookup search.

Hello Excel-experts,

In a table column A consists of unique product codes, sorted alphabetically. Each row of every product code consists of a non-descending list of whole numbers. Suppose A5 holds the product code "Adeel1-2", and the corresponding row (from B5 up to G5) holds the values 0, 2, 3, 6, 6, 10.

For any given product code, say "Adeel1-2", and any given number (not a whole number per se), say "4.2", I need the value of the cell "D6", the cell right below "D5", which holds the whole number smaller or equal to 4.2, in this case 3.

How can I find the correct row AND column numbers? Of course I do not know the row number of the product I am looking for and I also do not know the column number of the whole number smaller or equal to my search-number.

Any help would really be appreciated.

Kind regards,
Fie Buls

PL see attached file.

Hello Kvsrinivasamurthy,

Thanks for taking the time to take a look at my problem. I really appreciate it.

In your file "Ans.xlsx" you used the formula "=INDEX(B6:G6;VERGELIJKEN(4,2;B5:G5))". In this formula "VERGELIJKEN" is the Dutch word (I am Dutch, so that is good); I do not know the English version.

It seems that I have not stated my problem clearly enough. This formula will not search for the right product code first.

My problem:
In "Ans.xlsx" I will write in cell A1 the product code; a choice of "adeel1", "adeel2", "adeel3", "adeel4" or "adeel5".
In cell B1 I will write a number, for instance 4.2.

The required formula must first search the correct product code (of cell A1) in the range A5:A9. Once the correct row has been found the next search will be to find the cell-column-number in THAT row of the whole number which is smaller or equal to the value in cell B1. Suppose this will be the whole number in cell D5. Then finaly I need the cell-value of D6, which is the cell right below D5.


Hope my problem is now clearly stated. It essentially comes down to TWO searches. The second, horizontal, search must be done in a row of which you do NOT know the row-number in advance. It depends on the variable product-code.

I really hope someone can help me solve it.


Kind regard,
Fie Buls

Fie,

It would be easier to undestand if you could attach a sample workbook as Kvsrinivasamurthy has done in post #2, with the results you're looking for included in the relevant place.

YES!!!

Hello Stunn,

Your formula is EXACTLY what I am looking for. This works perfectly.

Now it is time for me to try to understand your formula (complicated looking for me ), but I will break it down piece by piece.


Seeing the results after trying and failing numerous times myself I think this is amazing; I really happy and a HUGE (!!!) thanks for your help (and to all others who have looked at the problem).


Kind regards,
Fie Buls


P.S. Since I am new to this, now I need to find out how I can post this as a SOLVED problem. I have put it in the title and hopefully that's how it is done.

If Stunn's formula works then try this one,

Formula:

Please Login or Register  to view this content.

Translated to Dutch

Formula:

Please Login or Register  to view this content.

@ Fiebuls

Based on your last post it seems that you are satisfied with the solution(s) you've received but you don't know how to mark your thread as SOLVED. I'll do that for you now. But the following is how to do it:

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Hello Jason,

This is absolutely GREAT! Your formula works like a charm and is much smaller. It took me almost an hour (with google searches) but I now even understand why your formula works.

In that process I have learned a lot from you.
I did not know you could set the col_num to zero in a index function to get this result. Also I did not know that you could have this kind of range-reference in a HLookup like B6:G6:$G$9.


Many, many thanks!!! Especially for taking the time to reply after I stated that my problem was solved. I really appreciate that you as experts take to time to help novices out like me.


Kind regards,
Fie Buls