[Solved] Get text from table, using specified key for lookup

Discuss the spreadsheet application
Post Reply
User avatar
pleides
Posts: 4
Joined: Fri Oct 03, 2014 5:22 pm

[Solved] Get text from table, using specified key for lookup

Post by pleides »

I have searched all day to no avail. There must be ability to do this. No numbers involved, just text for this.

I have a cell with a Distributor's Item. I want the spreadsheet to Insert into the column next to it, the name of the Distributor.
Or the name of the salesperson for that distributor.

Say I have a list of 10 Distributors, with 300 Items sold between them. How could I write a function to solve this.

I've tried IF statements. No luck so far.
True False statements return an Error, or simple words like False.

IFBLANK hasn't quite worked either.
It has the initial illusion to work, but doesn't in practice.
=IF(ISBLANK(L4);"";C4)

Lets say this:

Happy Wine - Buzz Distributor (G55)
Dark Wine - Dry Humor Distributor (G75)
Pink Wine - Manley Distributor (G35)
Green Wine - Too Early Distributor (G95)
Yellow Wine - Break Time Distributor (G105)

If A4 states "Happy Wine" as the item, then I would like A5 to say "Buzz Distributor" that is among the list of distributors, say in cell G55
If A4 states "Green Wine" as the item, then "Too Early Distributor" would show in A5

Thank you!
Last edited by pleides on Fri Oct 30, 2020 4:36 am, edited 2 times in total.
OpenOffice 4.0.1
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to insert Text from that Cell If This Cell Is..

Post by RusselB »

I'm going to suggest you consider using a LOOKUP table. Probably VLOOKUP or HLOOKUP
While an IF statement might work, it would end up being extremely long
As to the ISBLANK function, it only compares if L4 is blank. ie: No number, no text, and no formula
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
pleides
Posts: 4
Joined: Fri Oct 03, 2014 5:22 pm

Re: How to insert Text from that Cell If This Cell Is..

Post by pleides »

Thank you! It took a bit more re-learning this function. I haven't used this in awhile.

Here is my solution:
=VLOOKUP(K4;VLOOKUP.A1:B259;2;0)

This is the YouTube vid that finally got me there:
https://www.youtube.com/watch?v=WAEE2Z4Wz9E

I tried this tutorial page, may be helpful for others.
viewtopic.php?f=75&t=46746

Thank you Russel8
OpenOffice 4.0.1
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to insert Text from that Cell If This Cell Is..

Post by MrProgrammer »

pleides wrote:Here is my solution: =VLOOKUP(K4;VLOOKUP.A1:B259;2;0)
You're close. The formula you have will work OK for K4, but when you copy the formula to the cell below you'll get =VLOOKUP(K5;VLOOKUP.A2:B260;2;0) which is not what you will want. Your formula should be =VLOOKUP(K4;VLOOKUP.$A$1:$B$259;2;0). See Q19/A19 in the VLOOKUP questions and answers tutorial that you linked. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply