[Solved] Possible use of VLOOKUP

[kenppy]

I am making an assumption which may not be correct, but I couldn't get it to work.
The task is this:
1.I have a master sheet with col. BOX, essentially numbers 1 to 10,000 filtered ascending, not necessarily a full set.
2. For each BOX no. there is a corresponding cell NAME which will be filled by incoming data which for convenience will be on another sheet.
3. On both sheets I want to limit the range over which to act.In practice the blue box will most likely be longer.
4. Then for every line in "box" run through the lines in "BOX" and if numbers agree, place "name in "NAME".

Any thoughts?

[MrProgrammer]

Then for every line in "box" run through the lines in "BOX" and if numbers agree, place "name in "NAME".

I don't see why you wouldn't be able to use VLOOKUP for that. This sounds exactly what VLOOKUP does when you use the technique in Q2/A2. The function can be used with ranges in different sheets.
[Tutorial] VLOOKUP questions and answers

On both sheets I want to limit the range over which to act.In practice the blue box will most likely be longer.

No one can help with that until you explain in detail how the spreadsheet will know what the ranges for the blue and red boxes are. If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach.

I couldn't get it to work.

"It didn't work" isn't helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. We need to know exactly what actions you took, what did happen, and what you expected to happen. For example, if you typed a formula, pressed Enter, and got #VALUE!, tell us that, not "It didn't work." Then attach your document so we can see what's wrong with that formula. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[kenppy]

Now that I know VLOOKUP is the correct entry point I will look up the help and examples.
Thanks for the quick response.

[keme]

Note that VLooKUP() defaults to a kind of approximate matching and assumes that the key column is sorted. If there is no exact match, you will get the nearest lower value.
To force exact matching, you put zero in the fourth (optional) parameter to VLOOKUP(). No match will then return the #N/A error.

[kenppy]

I've had partial success and wanted to show you a file but can't find a way to reduce 680 to 128 KB. Forgot how this is done!

[MrProgrammer]

I have a master sheet with col. BOX, essentially numbers 1 to 10,000 filtered ascending, not necessarily a full set.

I've had partial success and wanted to show you a file but can't find a way to reduce 680 to 128 KB. Forgot how this is done!

We don't need the entire file of 10000 rows, just enough to show what you're having difficulty with. A hundred rows is probably plenty. Close your spreadsheet. Open it again, use File → Save As → Name → VLOOKUP for Forum to make a copy, leaving the original untouched. Ensure that you are editing VLOOKUP for Forum. Delete the excess rows, save it, and attach that. You can also delete excess columns in the sheet or excess sheets which have no bearing on the problem. Ensure that the smaller file still demonstrates the difficulty. Explain which cell on which sheet of the reduced file contains a formula that doesn't do what you want. Explain in English what you want it to do.

[kenppy]

snip.ods

shows principle

(13.36 KiB) Downloaded 124 times

I know that there is still the issue with maintaining blank cells but the min thing is why did 'fred' H9 not transfer to the new list? The 'value to search for' and 'table to search in' will have a diffment number of lines.

[karolus]

Hallo

You start with Formula:

Code: Select all

=VLOOKUP( B10 ; $G4:$H9 ; 2 ;0 )

BUT YOU SHOULD start with:

Code: Select all

=VLOOKUP( B10 ; $G$4:$H$9 ; 2 ; 0 )

and for Libreoffice you may skip the #NA's with:

Code: Select all

=IFNA(VLOOKUP( B10 ; $G$4:$H$9 ; 2 ; 0 ); "" )

[kenppy]

I don't understand, both of the last two codes give this:

[robleyd]

A sample spreadsheet would give some information; we can't tell anything from the picture as there is no information about what is in the formulae and where they are on the sheet.

[kenppy]

I provided the file earlier in the thread, I thought I was replying to karolus who suggested a couple of solutions. Here it is again with the suggested code.

snip2.ods

(13.23 KiB) Downloaded 130 times

[karolus]

Hallo

If you may read my last post carefully, you should see that »IFNA« is only for LIBREOFFICE but not for AOO!

[robleyd]

Your original upload didn't show this error; and without you showing the changed formula, it would have been a wild guessing game to determine the problem.

[kenppy]

I think I've got the solution now by going through the tutorials!
Copies as expected and blanks left blank instead of #N/A.

snip3.ods

(13.94 KiB) Downloaded 123 times