VLOOKUP and search name

Are you still using Excel 2013?

Please add manual some expected results and explain if neccessary how you derive these results

You expect on sheet loopup&results results.
Please add manually some expected results and explain how you how you determined those results based on the raw data, because I don't understand your description for 100%.

I don't see tab1, tab2 and I don't see expected results.

Good morning Hans, sorry for not better elaborating on that.
I have adapted the excel file with retrieved data from tab1 to tab2 using the VLOOKUP and see my main question (is it possible to use the VLOOKUP at same time searching in (Form: Last 6 matches),( Form: Last 8 matches) , (Form: Last 10 matches)

kind of asking Excel to search for (Form: Last 6 matches) and retrieve the data from the table belonging to (Form: Last 6 matches) it does not matter if the table from (Form: Last 6 matches) has been moved to other cells, which is the case because those table are not all time in the same cells, they move on daily base

and to search for (Form: Last 8 matches) and retrieve the data from the table belonging to (Form: Last 8 matches)


and the same applies for (Form: Last 10 matches)

Regards

Oulyas

Please try in B2 and copy down:

Formula:

Please Login or Register  to view this content.

Confirm formula with CTRL+SHIFT+ENTER

Thanks Hans, this is what i was looking for :-), it works fantastic.

Just a small issue with it

the formula doesn´t distinguish between: [Form: Last 4 matches (AT HOME)] and [Form: Last 4 matches (AWAY)]

and between [Form: Last 8 matches (AT HOME)] and [Form: Last 8 matches (AWAY)]

The values (AT HOME) and (AWAY) are different.

The formula is taking values from [Form: Last 4 matches (AT HOME)] for both (AT HOME) and for (AWAY) to tab2

Regards

Oulyas

the formula doesn´t distinguish between: [Form: Last 4 matches (AT HOME)] and [Form: Last 4 matches (AWAY)]

Indeed, such a case that cannot be recognized by the line being looked up (but can only be recognized by the position of the line) was not in your example.

Now you need a very different formula:

Please try

Formula:

Please Login or Register  to view this content.

In Excel 2013 is no SEQUENCE function available.

Please replace the SEQUENCE-function by the ROW-function:

Formula:

Please Login or Register  to view this content.

Unfortunately this function is much slower.

The LET function is not available in either Excel 2013 or Excel 2016.

I'm sorry I forgot to replace the LET-function:

FOR EXCEL 2013 please try:

Formula:

Please Login or Register  to view this content.

Now Excel understands the formula, because you get a #NV-error. : "Der Fehler #NV weist im Allgemeinen darauf hin, dass eine Formel nicht das finden kann, nach dem gesucht werden soll."
A #NV-error is in English a #N/A-error.

Please upload the sheet with the #NV error. Then I can figure out the cause.
Maybe not all your headers start with "FORM: " or EXCEL can't find the FORM: on the other sheet. Even one space too much or too little in your data can cause such an error.

The formula could not find the forms, because on sheet Forms there were spaces in the form name, but these were missing on sheet SW1.
Furthermore the formula could not find the teams, because they were on sheet SW1 surrounded by 2 CHAR(160)'s.
Furthermore, the form names and the team names were no longer in the same column.

After adjusting the team names, form names, and the references of the moved data, the formula works like a charm again. .

Did you confirm the formula with CTRL+SHIFT+ENTER and then press another time ENTER to execute the formula again?

If this does not work well, I advise you to add a help column with the name of the form on both sheets.
Then you can suffice with a very simple formula. See next post.

If you use Excel a lot then I advise you to upgrade to 365 (monthly channel).
Many new easy functions have been added there and the difference even with EXCEL 2021 is very big.

Here is a solution with a helper column (Column A) in both sheets with the name of the form.

And try in row 246 this formula

Formula:

Please Login or Register  to view this content.

Confirm the formula with CTRL+SHIFT+ENTER and then press another time ENTER to execute the formula again

Good luck tonight with Excel 2021.
I'm also curious if the solution in post #26 works in Excel 2013.

Good morning Hans, this works under Excel 2016 as you can see in "pic1" from "SW1", but the issue is in tab 2 "FORMS" see "pic2" when the data for example for "Form: Last 4 matches" moves down then the vlookup
do not work as it should, which means i will have to do it each time manualy to push "Form: Last 4 matches" , "Form: Last 6 matches", "Form: Last 10 matches"..etc
if those added cells in "FORMS" "A" are not deleted then the VLOOKUP will not work see "PIC3"

This is then an extra confirmation that there is something in the data that the formula does not expect or that the references in the formula are not correct.
You have to ask yourself whether the form names are written exactly the same, the team names are written exactly the same and that there are no weird characters anywhere and also that all references are exactly right.

I'd be happy to look into that for you, but please upload an example workbook that doesn't work. You can't see it from a picture.

I think working with help columns is a good choice to quickly and easily control the formulas. .

Replacing values is a new subject.
Select Values --> CTRL+F --> . --> Replace --> , --> Replace all

If I understand correctly then this seems to work:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Maybe try changing the commas ( , ) to semi-colons ( ; ).

I think Trevor is right. Try: Change ,1 and ,2 in ;1 and ;2

(You have changed the wrong komma(the only komma that was not a parameter-separator, but the komma you want in the number.)
So also change that semicolon back to a komma.

So

Formula:

Please Login or Register  to view this content.

Here is the file with the formula applied.
When the file is opened the formula should be adjusted to your regional settings.
Let us know if you have any questions.

See if this works for you. You may need to change the stop ( . ) to a comma ( , )

Formula:

Please Login or Register  to view this content.

returns 3 (numeric) for me. You can format it however you want to see decimal places.

You might need

Formula:

Please Login or Register  to view this content.

Or even

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the +reps.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, 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.