Lookup more than one column/cell

[Pauls123]

Hi, Using Excel 2010. I currently have a formula that looks like this:

=VLOOKUP(O2,$A$1:$B$9987,2,FALSE)

When this "lookup" takes place it of course gives me the data contained in column B relative to cell O2. Is there any way that this can also give me data contained in column C and maybe even further Columns (eg D E etc).

To be shown in cell/s to the right of P2

Regards, Paul

 

[Yard]

You mean you want the value from column C shown in Q2, the value from column D shown in R2 etc.?

Then this in P2:

=VLOOKUP($O2,$A$1:$B$9987,2,FALSE)

copy it to Q2, R2 etc., and just change the column reference (,2,) to 3 or 4 etc

Hope that helps

 

[Peter_SSs]

Or you can swap to INDEX/MATCH. Try this in cell P2 and copy across (and down if needed).

=INDEX(B$1:B$9987,MATCH($O2,$A$1:$A$9987,0))

 

[Peter_SSs]

You mean you want the value from column C shown in Q2, the value from column D shown in R2 etc.?

Then this in P2:

=VLOOKUP($O2,$A$1:$B$9987,2,FALSE)

copy it to Q2, R2 etc., and just change the column reference (,2,) to 3 or 4 etc

Hope that helps

You would, of course, have to also increase the lookup range to something greater than 2 columns.

 

[Pauls123]

Gentlemen, thank you very much. I've just tried both of those and Peter's one is nice and smooth. Can I go just another step further here, this is testing my luck.

When you do all these searches Excel always picks the first match it comes to. I've got data here where where a different cell/row has the same "name" in it, but Excel always selects the first one it comes to.

Naturally the same names are under each other as I have my lists alphabetically. Just that the names would have different data next to them. I am wondering how both names and corresponding data can show up, which would be difficult trying to get two or more rows of data onto one row,....!!

Regards, Paul

 

[Peter_SSs]

See if Aladin's suggestion here (post #3) is any use.

 

[Yard]

You would, of course, have to also increase the lookup range to something greater than 2 columns.

*blushes*

Yes, indeed.