Lookup on a Value in different columns

ASingh95

New Member
Joined
Apr 23, 2018
Messages
3
Hi,

I have a table on the left which shows a Van and which fruit goes in each van. What I want to do is replicate the right in terms of result, therefore having a list of fruit which I already have but using the table on the left show which van is carrying a particular fruit. (See Link for image)

https://ibb.co/h8Vqwx

Note this is with not much data my actual sheet is far larger with 100s rows and columns.

Thanks in Advance

Aaron
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,923
Office Version
365, 2019, 2016
Platform
Windows
Re: How to do a Lookup on a Value in different columns

try this

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Van</td><td style=";">Good1</td><td style=";">Good2</td><td style=";">Good3</td><td style=";">Good4</td><td style=";">Good5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Van</td><td style=";">Van</td><td style=";">Van</td><td style=";">Van</td><td style=";">Van</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style=";">Orange</td><td style=";">Bannana</td><td style=";">Pear</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apple</td><td style="background-color: #E2EFDA;;">B</td><td style="background-color: #E2EFDA;;">C</td><td style="background-color: #E2EFDA;;">D</td><td style="background-color: #E2EFDA;;">E</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">B</td><td style=";">Grapes</td><td style=";">Apple</td><td style=";">Pear</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Orange</td><td style="background-color: #E2EFDA;;">A</td><td style="background-color: #E2EFDA;;">C</td><td style="background-color: #E2EFDA;;">D</td><td style="background-color: #E2EFDA;;">E</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">C</td><td style=";">Orange</td><td style=";">Apple</td><td style=";">Grapes</td><td style=";">Pear</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Pear</td><td style="background-color: #E2EFDA;;">A</td><td style="background-color: #E2EFDA;;">B</td><td style="background-color: #E2EFDA;;">C</td><td style="background-color: #E2EFDA;;">D</td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">D</td><td style=";">Apple</td><td style=";">Orange</td><td style=";">Pear</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Grapes</td><td style="background-color: #E2EFDA;;">B</td><td style="background-color: #E2EFDA;;">C</td><td style="background-color: #E2EFDA;;"></td><td style="background-color: #E2EFDA;;"></td><td style="background-color: #E2EFDA;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">E</td><td style=";">Orange</td><td style=";">Bannana</td><td style=";">Apple</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Bannana</td><td style="background-color: #E2EFDA;;">A</td><td style="background-color: #E2EFDA;;">E</td><td style="background-color: #E2EFDA;;"></td><td style="background-color: #E2EFDA;;"></td><td style="background-color: #E2EFDA;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$6, SMALL(<font color="Green">IF(<font color="Purple">ISNUMBER(<font color="Teal">MATCH(<font color="#FF00FF">$B$2:$F$6, $H2, 0</font>)</font>), MATCH(<font color="Teal">ROW(<font color="#FF00FF">$B$2:$B$6</font>), ROW(<font color="#FF00FF">$B$2:$B$6</font>)</font>), ""</font>), COLUMNS(<font color="Purple">$A$1:A1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

ASingh95

New Member
Joined
Apr 23, 2018
Messages
3
Re: How to do a Lookup on a Value in different columns

Worked like a charm, thank you for your assistance.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,923
Office Version
365, 2019, 2016
Platform
Windows
Re: How to do a Lookup on a Value in different columns

you're welcome
 

Forum statistics

Threads
1,081,562
Messages
5,359,613
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top