Seeking Help on how to write a complex lookup formula

[Iccreamann21]

I want to say thank you for clicking on my post and and thank for any help you might be able to provide. What I am trying to accomplish is for an old BBS that I help assist run. I am trying to provide helpful information for the small user base that still continues to play.

Attached below is the excel workbook I am working on.

I wrote a vlookup, but ran into several problems:
1. there are other characters in the cell, I tried to do true instead of false for the range_lookup but was not successful.
2. there are also owners of the same item. So when I tried to do a vlookup it gives me the #NA because it sees multiple items with multi owners.

https://drive.google.com/open?id=17RF12cACS2aEiuTUt5cI50IqDoZZxQ2o

The first worksheet is the nice layout I would like to send to the users. The 2nd tab is the data I am able to copy from a txt file off the server.

Once again thank you for taking your time.

 

[Iccreamann21]

On the List worksheet i was using:
The vlookup i was using in cell F4 was: =VLOOKUP(B4,'Server List'!A:B,2,TRUE)
The vlookup i was using in cell F26 was: =VLOOKUP(B26,'Server List'!A:B,2,TRUE) .. i was also using this same in G26 and H26, but i receive #NA because it finds the item multiple times with different owners.

On the Server List worksheet:
I couldn't find a formula to put into B1:B220 to only pull out the owner names from A1:A220.

Hopefully this information will help a bit more.

Thank you

 

[mole999]

vlookup will only work with the first value in a sorted list. Have a read around INDEX / MATCH. Later you can fix the #NA wrapping formulas in =IFERROR( *****,"") but only when you have it working as required

 

[Marcílio_Lobão]

Iccreamann21, Good morning.

mole999, Good morning (Greetings from Brazil)

"On the Server List worksheet:I couldn't find a formula to put into B1:B220 to only pull out the owner names from A1:A220.

Try to use:
Server List --> B1 --> =IFERROR(MID(A1,SEARCH("USER ",A1)+5,500),"-----------")

Is that what you want?
I hope it helps.

 

[Iccreamann21]

Thank you, that was the solution I was looking for to extract the user name from the server data file.
Now I need help with the vlookup portion on the LIST tab. From what mole99 is saying I would need to use and index/match formula. Any suggestions on how to write that?
Attached is the updated workbook.
Thank you all so much for taking your time.

https://drive.google.com/open?id=15--VMxipVKcyd4aVk7CEVTE8eQtUZgf_

 

[Iccreamann21]

After browsing through a few posts I found a post with the similar issue. Here is what I tried, but still not working.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Antlered Helm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 336"]
<tbody>[TR]
[TD="width: 336"]Item belt of faith User Sprawl[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Belt Of Faith[/TD]
[TD]Sprawl[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Arcane Tome[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 336"]
<tbody>[TR]
[TD="width: 336"]Item antlered helm User Dread[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Antlered Helm[/TD]
[TD]Dread[/TD]
[TD]Dread[/TD]
[/TR]
[TR]
[TD]Antlered Helm[/TD]
[TD][TABLE="width: 336"]
<tbody>[TR]
[TD="width: 336"]Item crescent moon staff Map 15 Room 909[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Crescent Moon Staff[/TD]
[TD]Map 15 Room 909[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167"]Bloodstone Ring[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 336"]
<tbody>[TR]
[TD="width: 336"]Item holy medallion User TheDude[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Holy Medallion[/TD]
[TD]Frank[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


A: is list of items
B: is data pulled for text file
C: i tried to do a index/match , but I still get #NA .. i tried =INDEX(A:A,MATCH(B:B,A:A))
D: Works perfectly! I am able to pull the correct data.
E: Keep displaying the very first owner if there are multiple owners. =VLOOKUP(A:A,C:D,2,FALSE)

Sorry for being such a rookie. Any help would be appreciated and I you need more information. I will do my best to provide.

Thank you