Vlookup

WillCaton

New Member
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.

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

McGuilliam

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

Excel Formula:
``=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.

Attachments

• Screenshot 2020-10-23 130143.png
24 KB · Views: 2

WillCaton

New Member
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

MrExcel MVP, Moderator
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.

Replies
2
Views
74
Replies
4
Views
47
Replies
9
Views
168
Replies
11
Views
566
Replies
10
Views
302

1,127,100
Messages
5,622,722
Members
415,923
Latest member
Kam80

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.

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

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