How can I tell Match to return "text content" in cell instead of where it's located?

**NOBISAWYER** · 06-09-2012, 04:45 PM

Hi, I'm trying to use Vlookup & Match combination to grab text content in cells but it's not working well and I would like someone to help. I have List A, List B and List C. I'm trying to see if an item in list A occurs in List B. If it does, I want Vlookup to grab applicable item from List C and show it in another cell. Match will give me the location of the item in List B, but it does not return the content of the cell. Vlookup & Match combination will give me content of a wrong cell. I have example below:

List A List B List C
18AP NAN-A aa-21C
182ES 18AP za-11C
43TH3 042SP ey-31C

1. Comparing A list to B: Match for "18AP" is "2" for 2nd row. The code I need in List C is za-11C and it's correct. But my spreadsheet is 5K long and I don't want to count.
2. Vlookup will return "aa-21" but it's not the correct code.

How do I tell formula to get the correct code? Am I using a wrong combination? Any guidance is appreciated!

**shg** · 06-09-2012, 04:47 PM

What's the formula you're using?

**NOBISAWYER** · 06-09-2012, 04:54 PM

=VLOOKUP(A2,A1:C4,MATCH("c",A1:C1),FALSE)

**icestationzbra** · 06-09-2012, 04:59 PM

try:

=index($c$2:$c$4,match($a2,$b$2:$b$4,0))

or, you could also use your formula with a slight change:

=VLOOKUP($A2,$B$2:$C$4,2,FALSE)

**NOBISAWYER** · 06-09-2012, 05:04 PM

Worked! Thanks a million. Now, I need to go study up on Index function!

**icestationzbra** · 06-09-2012, 05:10 PM

you are welcome.

that's a good idea to 'look up' the INDEX function (pun not intended). it has many more features than VLOOKUP and is much more extensible. good tool to have in your panoply.

**NOBISAWYER** · 06-09-2012, 05:13 PM

Thanks, again! I had spent several hours trying to figure this out since early a.m.

**FDibbins** · 06-09-2012, 08:35 PM

icestationzebra, where can i get me 1 of those panoplolies??? and preferably 1 with lotsa stuff thats has been indexed?

**icestationzbra** · 06-09-2012, 10:34 PM

@fdibbins: that's easy, one of two ways to get it - either steal it from the likes of NBVC... or, (l)earn by doing the hard yards...

**FDibbins** · 06-10-2012, 03:52 PM

good advice, you think shg would notice his missing too?

**icestationzbra** · 06-10-2012, 03:55 PM

he is not as active here as he used to be, so he may not notice that his is missing from here :P

How can I tell Match to return "text content" in cell instead of where it's located?

LinkBack

Thread Tools

Rate This Thread

Display

How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Re: How can I tell Match to return "text content" in cell instead of where it's located?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions