# VLOOKUP to search multiple columns and return value from single column

1. ## VLOOKUP to search multiple columns and return value from single column

Hello.

I am trying to create an equation that can do a search of multiple columns and return the value from a single column that may be associated to another (for example, if I search columns A, B and C and find the matching value in column C, I would like to only return the associated value in Column A). I have attached an Excel file of example data and the format that it would be in. I understand that the explanation is a little confusing, so please let me know if further clarification is need.

2. ## Re: VLOOKUP to search multiple columns and return value from single column

If I understand the need correctly, you want to search for the values in column A of List A and find them ANYWHERE on sheet Master List , and whereever it is found, you want to the get back the value from column A on the Master List.

If that is correct, then put this array formula in C2 of List A:

=IF(COUNTIF('Master List'!\$A\$2:\$H\$2000, \$A2)=0, "", INDEX('Master List'!\$A\$2:\$A\$2000, MAX(IF(A2='Master List'!\$A\$2:\$H\$2000, ROW('Master List'!\$A\$2:\$H\$2000)-1))))

...and confirm it by pressing CTRL-SHIFT-ENTER to activate the array, you will see curly braces { } appear around your formula.

Now copy C2 downward and the ones that are found will give you the Primary Synonym.

3. ## Re: VLOOKUP to search multiple columns and return value from single column

Ok here's one way, there's probably better ways

on your List A sheet in B2
=IF(ISNA(MATCH(\$A2,'Master List'!A\$1:A\$1189,0)),0,MATCH(\$A2,'Master List'!A\$1:A\$1189,0)+(COLUMN()-1)/10)
and copy across to column I

in J2
=MAX(B2:I2)

in K2
=IF(J2=0,"NOT IN MASTER LIST",INDEX('Master List'!A\$1:A\$1189,INT(J2),1))

in L2
=INDEX('Master List'!A1:H1,1,RIGHT(J2,1)*1)

Now copy B2:L2 down as far as row 184, the last row in List A

What this does is searches the columns on the Master List for your items on List A and returns a decimal number.
The first part of the decimal number is the vertical position in the list.
The second part of the decimal is the Master List column it was found on,. Primary, Ancillary 1, Ancillary 2 etc
So 4.1 means its associated position in the Primary list is the 4th item down,
and the .1 means it was found on the Primary list, .2 would be the next column, ie Ancillary 1

Your results will appear in column K and L
Column K indicates the associated value in the Primary column
Column L indicates what list it appeared in.

Please note in your List A you have values that do not appear in the Master List at all (see Ankle Fusion) and you also have values that appear in several columns of the Master List (see Ankle Arthroscopy)

Use Ctrl-F (Find) to check this

4. ## Re: VLOOKUP to search multiple columns and return value from single column

JBeaucaire,

Yes, you understand what I was looking for EXACTLY. However, when I entered this formula, it was returning results that didn't seem to match...the value returned from the formula did not match with the value in column A of List A and the corresponding values from the Master List. I have attached a copy of my Excel document after inserting the formula

5. ## Re: VLOOKUP to search multiple columns and return value from single column

JBeaucaire's formula works fine.. You need to ensure that you confirm the formula with Ctrl+Shift+Enter as its an array formula

See JBeaucaire's post
and confirm it by pressing CTRL-SHIFT-ENTER to activate the array, you will see curly braces { } appear around your formula.

6. ## Re: VLOOKUP to search multiple columns and return value from single column

Did you get the CSE working? (Ctrl-Shift-Enter)

7. ## Re: VLOOKUP to search multiple columns and return value from single column

JBeaucaire,

Yes, I was able to make the CSE work. However, when applying the equation to another document, the formula is returning incorrect values...is there anything that needs to be altered when used in a different file? Does it have anything to do with the fact that it is an array formula?

8. ## Re: VLOOKUP to search multiple columns and return value from single column

Yes, the formula has to be completely edited if the ranges do not suffice. I set it for 2000 rows starting at row2. What does it need to be?

There is a -1 at the end of the formula that needs to be adjusted for the first row where the formula range evaluates. If the first row in the ranges is row2, then -1 is correct. If the first row in the ranges is row8, then -7 would be the correct edit.

9. ## Re: VLOOKUP to search multiple columns and return value from single column

That did the trick! Thank you again for all of your help! It is much appreciated

10. ## Re: VLOOKUP to search multiple columns and return value from single column

@ BBExcelusr

Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1