Vlookup (or other) with blanks in columns

rduncan2019

New Member
Joined
Mar 4, 2019
Messages
2
Hello,

Apologies if this has been asked before, I may not be using the best key words to search for. I have a form which has been filled out by people and the form responses are outputted to an excel file. I want to search this file for the entries made by specific people and at first thought of a vlookup to do it. However I do not know which column will contain the information and wanted a formula to search that would skip the blanks in the row and return the first entry it finds.

My data table is as such:

Column A - unique user ID to search on
Columns B to F - one of these columns, and only one of these, will contain an entry and I am looking for that entry to be returned; data is a text string

Thank you!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome to the Board!

Try:


ABCDEFGHIJ
1User IDUser IDResponse
21one1one
32two2two
43four3four
54three
65six
76five
87seven

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
I2=LOOKUP("zzzzzz",INDEX($B$2:$F$10,MATCH(H2,$A$2:$A$10,0),0))

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
That worked beautifully, thank you! At first I thought I needed to replace the "zzzzzz" as they were a placeholder for a cell but it works just like that!
 
Upvote 0
That worked beautifully, thank you! At first I thought I needed to replace the "zzzzzz" as they were a placeholder for a cell but it works just like that!
Indeed! If you are looking for a text value, that parameter needs to be something that will be after anything you might find in your data. So "zzzzzz" will generally fill the bill!

Glad to help! :cool:
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top