Lookup in Multiple Columns and return a string

[Beh162]

I have a table that pulls data in, but it's in a random order. I've been trying to use a lookup functions that will reorganize it, but because one of the text it's looking up isn't in the first column I'm getting alot of wrong results.

I need to search A2 and look through F2:H16 and when it finds a hit, it will copy F:H as is in that order (Hopefully it's possible) I've attached a picture and in A:D is how I'd hope the results would go.


Screen Shot 2017-08-30 at 5.25.47 PM.png

[AliGW]

Try this in B2 and drag copy across to D2 and then down:

=VLOOKUP(A2,$F2:F16,COLUMNS($B2:B2),0)

[Beh162]

I was using the vlookup and it seems when I get to Raiders, it comes up with an error because it's in Column H and not F.

I'm currently trying Match/Index to see if that will do what is needed

[AliGW]

Please clarify the order in which you want to display the data. It is not clear.

[Beh162]

The correct order is how A2:A4, but I need it to still keep the order of how it appears in the rows in F:H

So I need to find Raiders and return Titans 1 Raiders
Find Cards, Returns Cards 1 Lions
Is this even a possibility ?

[AliGW]

Yes, but what I am getting at is how will the list grow? What comes next after Raiders. There is no logic I can see in the order in which the teams are appearing in column A. What am I missing?

You will need to establish an INDEX MATCH that first looks up column F and if it finds nothing, column H. Once you have established which row the searched for team is on, the rest is easy. I'm afraid I'm away from my PC now, and have run out of time this evening.

[FlameRetired]

Beh162 please upload a workbook. It saves retyping data. I have an idea, but am busy doing other formulas as well.

[Beh162]

Okay I've uploaded a sample book.

I'm currently working on Match and Index to see how that may work for this.

[Root_]

Try the following in your Test Workbook:

- Select range B1:D1
- Paste this formula in the Formula Bar =INDEX($F$1:$H$4,MATCH("*"&A1&"*",$F$1:$F$4&$H$1:$H$4,0),0)
- Press Cmd+Return to enter the array formula (note: please check your Excel help for the key combination)
- Drag-copy the three selected cells down

[Beh162]

I got an NA error. Would array be my best option, or would other formula's work without Array? Curious

[FlameRetired]

Like AliGW suggests make room for growth. A dynamic named range is good for this.

In Name Manager this formula which I named TeamList

Please Login or Register  to view this content.

Then this formula in B1 filled down.

Please Login or Register  to view this content.

[Beh162]

Okay awesome - I'm going to look into this and put it into my live data and see.

I appreciate the help alot

[FlameRetired]

Before you get away here's a simpler formula. It does have to be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

With the same dynamic named range.

Please Login or Register  to view this content.

[Beh162]

I will also practice on that. I do like the addition of dynamic named range. I never explored that

[FlameRetired]

Late Edit: Ignore this one. It won't handle return matches. FR

Here's a shorter one. It does not have to be array entered.

Please Login or Register  to view this content.

[Phuocam]

Or try this ...

=INDEX(F:F,SMALL(IF($F$1:$H$100=$A1,ROW($F$1:$H$100)),1))

Enter with Ctrl+Shift+Enter

[FlameRetired]

@ Phuocam How do you index for column H when items in column A are in column F?

[Phuocam]

@ Phuocam How do you index for column H when items in column A are in column F?

Maybe?

=INDEX(INDIRECT(IF(LARGE(IF($F$1:$H$100=$A1,COLUMN($F$1:$H$100)),1)=8,"F:F","H:H")),
LARGE(IF($F$1:$H$100=$A1,ROW($F$1:$H$100)),1))

[FlameRetired]

OK. Why INDIRECT? Your formula works without it.

[AliGW]

Try this (enter each separately, then drag copy all three down):

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Jets	Jets	7	Bills
2	Falcons	Falcons	5	Bears
3	Raiders	Raiders	1	Titans
4	Patriots	Patriots	8	Chiefs

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	B	C	D
1	=IFNA(INDEX($F$1:F$4,MATCH($A1,$F$1:$F$4,0)),INDEX($H$1:H$4,MATCH($A1,$H$1:$H$4,0)))	=INDEX($G$1:G$4,IFERROR(MATCH($A1,$F$1:$F$4,0),MATCH($A1,$H$1:$H$4,0)))	=IFNA(INDEX($F$1:F$4,MATCH($A1,$H$1:$H$4,0)),INDEX($H$1:H$4,MATCH($A1,$F$1:$F$4,0)))

Sheet: Sheet1

[Beh162]

AliGM

This one did work, but the problem is when it searched for Raiders it reverse how it needed to appear. It should be Titans 1 Raiders.

[AliGW]

Explain your logic for Titans coming first.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
1	Jets	Jets	7	Bills		Patriots	8	Chiefs
2	Falcons	Falcons	5	Bears		Bills	7	Jets
3	Raiders	Raiders	1	Titans		Falcons	5	Bears
4	Patriots	Patriots	8	Chiefs		Titans	1	Raiders

Sheet: Sheet1

[AliGW]

Try changing B1 to this:

=IFNA(INDEX($F$1:F$4,MATCH($A1,$F$1:$F$4,0)),INDEX($F$1:F$4,MATCH($A1,$H$1:$H$4,0)))

and D1 to this:

=IFNA(INDEX($H$1:H$4,MATCH($A1,$F$1:$F$4,0)),INDEX($H$1:H$4,MATCH($A1,$H$1:$H$4,0)))

[AliGW]

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.