VLOOKUP formula help to append multiple columns to data range

I have two sheets in my excel. Sheet 1 Column1 contains the same data as Sheet2 Column2 "Identifier"

When "Identifier" from sheet one matches that of sheet2 I want to capture columns 2,3,4,5 from sheet one in that row and append them to sheet2


Help!

Many thanks

Put this formula in C2 of Sheet2:

=IFERROR(VLOOKUP($B2,Sheet1!$A:$E,COLUMNS($B:C),0),"")

then you can copy this across and down as required.

Hope this helps.

Pete

Excel.JPG

So I need to append columns B,C,D from sheet1 to sheet2 when PSA Identifier from sheet1 matches that in sheet2

It's not clear where you want them to go (i.e. in which columns), but the same basic formula that I gave you should accomplish that - you might need to change the $B2 to suit the row that you start on.

Pete

Hi,

VLOOKUP is what you need and a neat description follows on How to Use a VLOOKUP

In your example you'll need a vlookup for each of the columns whose values you want to return (see "WhichColumn" below)

A VLOOKUP looks for a value in the leftmost column of a table and then returns a value from the row that the searched for value is found (how far along that row is determined by you).

The 4 parts to a VLOOKUP formula:

SearchFor this is the value that is being searched for.

WhereToSearch this is the range in which to search and in which the answer lives.

WhichColumn this is the column in the WhereToSearch range which has the answer in it.

NearOrExact you decide whether the VLOOKUP should search for a close value or an exact value.

Structure of the VLOOKUP formula:

=VLOOKUP ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )

... so your formula might look a little like

=VLOOKUP($B2,Sheet1!$A:$E,2, False ) & ", " & VLOOKUP($B2,Sheet1!$A:$E,3, False ) & ", " & VLOOKUP($B2,Sheet1!$A:$E,4, False ) & ", " & VLOOKUP($B2,Sheet1!$A:$E,5, False)

Source: How to use a vlookup