# Formula to find data based on information in two different columns

Copper Contributor

# Formula to find data based on information in two different columns

I'm struggling to find the correct formula to get me the data as below:.

I have column A, B and C.

Column A has a persons name, Column B has an address, and Column C has a specific size.

Basically, I am trying to get a formula that will identify a name in A, then a size in C, which will then enter the address in B in the cell of my choice.

E.g., If 'Ashleigh' is in cell A, and size 20 is in column C, then my cell should be entered New Zealand.

Is this possible?

pic #1 is the data i'm collecting, pic#2 is the cell i am wanting the address in

11 Replies

# Re: Formula to find data based on information in two different columns

Let's say the data in your first picture are in A2:C1000 (with A1:C1 being the headers) on a sheet named Data Sheet.

On another sheet, you enter a name (or select it from a Data Validation drop-down) in A2 and a size in C2.

Enter the following formula in B2:

=XLOOKUP(A2&C2, 'Data Sheet'!\$A\$2:\$A\$1000&'Data Sheet'!\$C\$2:\$C\$1000, "")

This can be filled or copied down as far as you want.

# Re: Formula to find data based on information in two different columns

Hi Hans,
Thanks so much. I'm stuck on the copying from another sheet part.. I have the sheet with all the data on it called 'DUMP', however struggling to figure out how to get the formula to pick up this is the sheet?
This is what I have changed it to =XLOOKUP(A2&C2, 'DUMP'!\$A\$2:\$A\$1000&'DUMP'!\$C\$2:\$C\$1000, "")

# Re: Formula to find data based on information in two different columns

Does it work? If not, could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Formula to find data based on information in two different columns

I have just uploaded to the original post 'Sample' :)
You can see the DUMP tab, has the informaiton given, and it needs to pull the address over to two parts of the document Tab B

# Re: Formula to find data based on information in two different columns

I think I completely misunderstood your question. Does the attached version do what you want?

# Re: Formula to find data based on information in two different columns

Oh my gosh it DOES! Thanks Hans,
However my now issue is that I have about 10x pages of different names that I need the formula to work for (Jacob was one, however I have booking forms for more people).
How can I adjust the filter to work for each name?

# Re: Formula to find data based on information in two different columns

The formulas use the name in A1. If you enter another name in A1, the formulas will automatically display the results for that name.

So you could create copies of the sheet, and change the name in A1.

# Re: Formula to find data based on information in two different columns

Hi Hans,
For some reason I still can't get it to work? If I add another 'Jacob' to the dump sheet, with details, it won't add on to the 'Jacob' sheet?
I'm thinking it may be easier to make 2x dump sheets to separate off the 1/28's and the FP/HP? so we don't have to pull through that data within the formula?

# Re: Formula to find data based on information in two different columns

I may have explained this badly..
I'm thinking instead, if I can identify the name from column A, and have the formula auto enter the address from column B in my 'sheet two' ?

# Re: Formula to find data based on information in two different columns

my 'sheet two' ?
Sorry,I can not understand what you need.