Vlookup

WillCaton

New Member
Joined
Oct 21, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Example
I wish to find the best way to add the following data
LookupPop
abcPop21
defPop22
ghiPop23
jklPop24
mnoPop25
pqrPop26
stuPop27

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

NameKey fieldA1A2A3A4A5A6
RedabcPop1
WhitedefPop2Pop3
BlueghiPop4Pop5Pop6
GreenjklPop7Pop8Pop9Pop10
OrangemnoPop11Pop12Pop13Pop14Pop15
PurplepqrPop16Pop17Pop18
BrownstuPop19Pop20
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
NameKey fieldA1A2A3A4A5A6
RedabcPop1Pop21
WhitedefPop2Pop3Pop22
BlueghiPop4Pop5Pop6Pop23
GreenjklPop7Pop8Pop9Pop10Pop24
OrangemnoPop11Pop12Pop13Pop14Pop15Pop25
PurplepqrPop16Pop17Pop18Pop26
BrownstuPop19Pop20Pop27

the solution should avoid visual basic and macros.

Thanks for looking.
 

Some videos you may like

Excel Facts

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

McGuilliam

New Member
Joined
Oct 23, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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
    Screenshot 2020-10-23 130143.png
    24 KB · Views: 2

WillCaton

New Member
Joined
Oct 21, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top