VLOOKUP Multiple Column Challenge

[Logit]

I am familiar with using VLOOKUP and populating data into one column. What I don't understand is how to populate multiple columns.

The attached demonstrates a user entering "3D2" into the search field. The desired effect is for the formula to return any matching data in column N from the data to the right.

- How do I get the formula to return all seven columns of information pertaining to the search criteria ?

- The final intended sheet layout (rows / columns) is the same as shown.

- The search criteria will never exceed the first four characters on the left (i.e., 2E0G but not 2E0GW - refer N22 - )

I've tried referring to examples on the Forum and from around the 'net' but I'm not having any success. Thank you for your assistance !

[loginjmor]

Hi -

Can you just use VLOOKUP like this:

=VLOOKUP($G$11,$N$17:$T$15853,2)

Paste this formula in cell F17. Copy across and change the last digit from 2 to 3 in cell G17 so it looks like this:

=VLOOKUP($G$11,$N$17:$T$15853,3)

Change the last digit to 4 in cell H17, 5 in I17, 6 in J17, and 7 in K17.

Or am I not understanding what you're looking for?

Hope this helps.

[FDibbins]

Im a bit confused by what you want?

Which table is your data table
Where do you want the answers?
What do you mean by

The search criteria will never exceed the first four characters on the left (i.e., 2E0G but not 2E0GW - refer N22 - )

vlookup will search for whatever you enter, and will always only ever return ONE match

[Logit]

Which table is your data table? The table on the right, in light grey.

Where do you want the answers? Displayed in cells E17:K17 and below.

What do you mean by: "The search criteria will never exceed the first four characters on the left (i.e., 2E0G but not 2E0GW - refer N22 - )
vlookup will search for whatever you enter, and will always only ever return ONE match" ?

- Be able to search just the first four characters (starting from the left side) residing in cells N17:N15853.

- An example is always best for demonstration .... refer to Sheet 2 in the attached that explains what I mean ...

Thank you for your help.

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[Logit]

I'm only getting the country displayed.

I've seen from examples on the net / Forum where the formula can be pasted into E17 then copied down as many rows as needed in column E with
incrementing row numbers :

=VLOOKUP(LEFT($E$17,4),$N$17:$T$15853,{2,3,4,5,6,7},FALSE)
=VLOOKUP(LEFT($E$18,4),$N$17:$T$15853,{2,3,4,5,6,7},FALSE)
=VLOOKUP(LEFT($E$19,4),$N$17:$T$15853,{2,3,4,5,6,7},FALSE)

This allows multiple returns on the search data if they exist. Using the last file attachment "VLOOKUP Multiple Columns 2.xlsx‎" Sheet #2, click on the blank rows in columns B, C or D and see how the formula increments.

Isn't there a way to place the formula in the formula bar rather than referring to a Sub using VBA ?

Thank you for your attention.

[JOHN H. DAVIS]

There may be, but the only way I've been able to get the formula array for VLOOKUP too work is with VBA.

[Logit]

Thank you for looking. Perhaps someone else ?

[Logit]

Additional internet research reveals instead of VLOOKUP the formula should be using INDEX.

An example of a resource I am referring to is attached. However, this formula generates an error message : "A value used in the formula is of the wrong data type." Curious it works in the example file but not in
mine. I've checked and recheck my typing - hope I didn't overlook anything.

Here is my attempt at the formula:

Please Login or Register  to view this content.

What am I doing wrong ?

[sanram]

I have corrected that formula for you. You need a helper column there as you will write only 1st 3 letters. See the attachment.

[Logit]

Sanram:

Thank you so much for the assistance. I didn't realize until now, with over 15,000 data elements, how long it takes to "write" the search results
to screen. Is there a means to make this faster (other than shortening my data list) ?

[sanram]

You can use VBA to do so, which will take only few seconds to generate this.

[Logit]

Would using VBA still allow me to use the cell G11 for entering the search criteria or would it be better to have a text box and a command button for the user to click on ?

[Logit]

Would the VBA code go into the FORM or a Module ?

[sanram]

Use the VBA on worksheet. Not on module or form. Use Worksheet_Change()

See the attachment.

[Logit]

WOW ! That is FAST !!! Thank you again for your help. You are a valuable resource.