Siimple Match/Index/Vlookup (not sure which one?) formula...

[mlarson]

Note: having problems making the data look like an Excel chart... hopefully it's clearer than mud

Thanks in advance for your help! I'm not sure if this is a match, index, vlookup? But I'm guessing it's pretty simple to do.

I'm looking for a formula that will do the following:

Match the text in cells in Column C with the text in cells in Column Q. When there's a match, take the value in Column P that is in the same row of the matched Column Q cell, and copy/paste that Column P value in Column B of the same row as the matched Column C cell. Column B can be left blank if there's no match.

Column B is output
B C P Q
71 green 72 white
88 blue 65 violet
34 yellow 71 green
(blank) orange 88 blue
72 white 92 pink
(blank) black 34 yellow

Thank you!

 

[philwojo]

Try this:

=IFERROR(INDEX($P$8:$Q$13,MATCH(C8,$Q$8:$Q$13,0),1),"")

Update the cell references to reflect your actual cell numbers.

 

[mlarson]

Thank you! Does that work for cells with text (yellow, blue, etc.)? I ask because I've spent the last hour copying and pasting one of the columns into the spreadsheet (column Q) and it doesn't seem to match. I've tried cut and paste into Notepad and back into Excel. I've tried making sure both column C and Q are formatted the same as General, then as Text. No hyperlinks. Formatting, spacing, etc. seem to be the same. Any ideas? Thanks!

 

[philwojo]

That should work for the text in the cells, yes. As written it is going to look at the contents of what is in Cell C# (replace # with the row#) and compare it to the range specified for Q, currently Q8-Q13. You will need to expand or update that range to reflect your actual range.

If it finds a match from C# in your Q range it will then return the value from column-P from the row# that it matched to.
If it finds no match from C# in your Q range it will just show as a blank cell.


What results are you getting?
Can you post the specific formula you put in?

 

[mlarson]

The results I'm getting (in column B) is that they are all blank. When I manually type a name in a cell in column Q then the result appears in Column B, but there is too much data to type it all manually.

This is the formula I entered in B2 and copied down to B1000... =IFERROR(INDEX($P$2:$Q$1000,MATCH(C2,$Q$2:$Q$1000,0),1),"")

Note: the words in column Q are actually names (Last, First) but I replaced that with colors when posting on this thread for the sake of privacy. Perhaps matching "Doe, John" messes up where matching "yellow" would work?

 

[philwojo]

The value in C2 has to exactly match a value in Q2:Q1000 otherwise it will give you the blank.

So, we can tweak the formula some, but do you know what the difference is between what is in C2 and what is in Q now and what changed when you typed the value in Q?

Is it a trailing space or extra characters?

 

[mlarson]

It's the trailing space in column Q, great find! I tried everything else I could think of but you found it. Is there a way to remove the blank trailing space in column Q? Or, if it's easier, just tweak the formula to match while leaving the trailing space alone.

 

[philwojo]

So if you do a Text-to-Columns on column-Q and use the "space" as the deliminator it will remove it, but that is assuming you don't have anything in column-R.

Another way would be to use another column and put this formula in it:

[COLOR=#333333]=tirm(q2)[/COLOR]

Then copy and paste it down to say X1000 (assuming starting in X2).

then once that is done copy those results and paste "Values only" in to q2:q1000 and it should remove the space.

 

[mlarson]

Thanks! I used =trim(q2) and it worked. Thank you very much!

 

[philwojo]

Glad that worked and then assuming that the main formula is now working as you expect as well?