Vlookup

[WillCaton]

Example
I wish to find the best way to add the following data

Lookup	Pop
abc	Pop21
def	Pop22
ghi	Pop23
jkl	Pop24
mno	Pop25
pqr	Pop26
stu	Pop27

to the end of the correct row in the next available empty cell. The problem is each row is a different length

Name	Key field	A1	A2	A3	A4	A5	A6
Red	abc	Pop1
White	def	Pop2	Pop3
Blue	ghi	Pop4	Pop5	Pop6
Green	jkl	Pop7	Pop8	Pop9	Pop10
Orange	mno	Pop11	Pop12	Pop13	Pop14	Pop15
Purple	pqr	Pop16	Pop17	Pop18
Brown	stu	Pop19	Pop20

Objective

place the lookup value in the next available blank cell for each row. Problem is that each row is a different length. if a solution could be found the end result would look like this

Name	Key field	A1	A2	A3	A4	A5	A6
Red	abc	Pop1	Pop21
White	def	Pop2	Pop3	Pop22
Blue	ghi	Pop4	Pop5	Pop6	Pop23
Green	jkl	Pop7	Pop8	Pop9	Pop10	Pop24
Orange	mno	Pop11	Pop12	Pop13	Pop14	Pop15	Pop25
Purple	pqr	Pop16	Pop17	Pop18	Pop26
Brown	stu	Pop19	Pop20	Pop27

the solution should avoid visual basic and macros.

Thanks for looking.

 

[McGuilliam]

If there can be a formula in the empty cells, this might work:

=IF(COUNTIF($C13:D13;"="&INDEX($B$2:$B$8;MATCH($B13;$A$2:$A$8;0)));"";INDEX($B$2:$B$8;MATCH($B13;$A$2:$A$8;0)))

This is with the build as seen in the image, of course the cell references should be adjusted to your sheet.

If needed the formula can be overwritten. If you need the cells to be completely empty you need VBA.

 

[WillCaton]

Thank you for replying I will take a look. I have not used matching or index before I will find out and see if I can get this to work.

 

[Peter_SSs]

The solution should avoid visual basic and macros.

Welcome to the MrExcel board!

If I have understood correctly & you already have some data/results in place then it cannot be done without vba/macros.