# Vlookup equivalent code

**URL:** <https://forum.wixstudio.com/t/vlookup-equivalent-code/19302>\
**Category:** Ask the community\
**Tags:** question, code\
**Created:** [May 20, 2019, 9:24pm UTC](https://forum.wixstudio.com/t/vlookup-equivalent-code/19302 "2019-05-20T21:24:37Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![stephenxj](https://sea1.discourse-cdn.com/wix/user_avatar/forum.wixstudio.com/stephenxj/32/12006_2.png) [@stephenxj](https://forum.wixstudio.com/u/stephenxj)\
**Post date:** [May 20, 2019, 9:24pm UTC](https://forum.wixstudio.com/t/vlookup-equivalent-code/19302/1 "2019-05-20T21:24:37Z")

</div>

Hi,

I am looking for an sample code to achieve the function similar to “vlookup” in excel, but couldn’t find any, was wondering if anyone could provide me with an example or direct me to an existing one? What I want to achieve is-

One the webpage, I have a “table” and a “dropdown” filter linked to the table.  
when value A is selected in the dropdown, code will search in the “data collection” column “I” and pick up corresponding value B from column “II”. Value B will be used as a filter for the “table”.

Thanks.

Regards,  
Stephen

---

<div class="post-metadata">

**Author:** ![givemeawhisky](https://sea1.discourse-cdn.com/wix/user_avatar/forum.wixstudio.com/givemeawhisky/32/5383_2.png) [@givemeawhisky](https://forum.wixstudio.com/u/givemeawhisky)\
**Post date:** [May 20, 2019, 10:33pm UTC](https://forum.wixstudio.com/t/vlookup-equivalent-code/19302/2 "2019-05-20T22:33:02Z")

</div>

You can filter a table by dropdowns as like in these tutorials.  
[https://codequeen.wixsite.com/dropdown](https://codequeen.wixsite.com/dropdown)  
[https://www.youtube.com/watch?v=EhXed0u6wh0](https://www.youtube.com/watch?v=EhXed0u6wh0)

Repeater filtering.  
[https://www.youtube.com/watch?v=r0DLqkRDJ34](https://www.youtube.com/watch?v=r0DLqkRDJ34)

---

<div class="post-metadata">

**Author:** ![stephenxj](https://sea1.discourse-cdn.com/wix/user_avatar/forum.wixstudio.com/stephenxj/32/12006_2.png) [@stephenxj](https://forum.wixstudio.com/u/stephenxj)\
**Post date:** [May 20, 2019, 11:32pm UTC](https://forum.wixstudio.com/t/vlookup-equivalent-code/19302/3 "2019-05-20T23:32:06Z")

</div>

Many thanks for your reply. I’ve seen both in the past, but don’t think the “Vlookup” function is mentioned in either of them.

My filter is working fine.

---

<div class="post-metadata">

**Author:** ![J.D](https://sea1.discourse-cdn.com/wix/user_avatar/forum.wixstudio.com/j.d/32/7312_2.png) [@J.D](https://forum.wixstudio.com/u/J.D)\
**Post date:** [May 20, 2019, 11:58pm UTC](https://forum.wixstudio.com/t/vlookup-equivalent-code/19302/4 "2019-05-20T23:58:02Z")

</div>

@stephenxj I guess you can always use some JavaScript like:

```javascript
//Let's say you want to find the last name of John:
const matchRow = $w("#myTable").rows.find( e => e.firstName === 'John' );
const requiredValue = matchRow.lastName;

```

---

<div class="post-metadata">

**Author:** ![stephenxj](https://sea1.discourse-cdn.com/wix/user_avatar/forum.wixstudio.com/stephenxj/32/12006_2.png) [@stephenxj](https://forum.wixstudio.com/u/stephenxj)\
**Post date:** [May 27, 2019, 11:15pm UTC](https://forum.wixstudio.com/t/vlookup-equivalent-code/19302/5 "2019-05-27T23:15:53Z")

</div>

@jonatandor35 very helpful thanks. A follow up question hope you have a bit time to help. my draft website is at [https://stephenxj5.wixsite.com/yyeducation/copy-of-school-search](https://stephenxj5.wixsite.com/yyeducation/copy-of-school-search)

It’s designed to contain 2 steps.

- Step 1 in the current school section which is linked to collection"Chin\_School": choose the province then the school, the “vlookup” function you helped me earlier will pick up a value from the table - requiredValue = matchRow.cat
- Step 2 in the target school section which is linked to dataset “dataset1”: apart from the visible filtering “Gender”, “Town”, “Postcode” as you can see, there is an additional hidden filtering - “Cat” equals to requiredValue from step1.

2 Questions:

- the section where the requiredValue is retrieved in step1 might not be correct
- when I refer to requiredValue in step2, it says “requiredValue is not defined”.

Please see the codes attached below.

import wixData from ‘wix-data’;

$w.onReady( function () {

```
uniqueDropDown1(); 

```

});

function uniqueDropDown1 (){

```
wixData.query("Chin_Schools") 

    .limit(1000) 

  .find() 

  .then(results => { 

```

const uniqueTitles = getUniqueTitles(results.items);

```
       $w("#province").options = buildOptions(uniqueTitles); 

  }); 

```

function getUniqueTitles(items) {

const titlesOnly = items.map(item =\> item.province);

**return** [… **new** Set(titlesOnly)];

```
} 

```

function buildOptions(uniqueList) {

**return** uniqueList.map(curr =\> {

**return** {label:curr, value:curr};

```
    }); 

} 

```

}

export function province\_change(event,$w) {

uniqueDropDown2();

$w(“#schooldrop”).enable();

}

function uniqueDropDown2 (){

```
wixData.query("Chin_Schools") 

    .contains("province", $w("#province").value) 

    .limit(1000) 

  .find() 

  .then(results => { 

```

const uniqueTitles = getUniqueTitles(results.items);

```
       $w("#schooldrop").options = buildOptions(uniqueTitles); 

  }); 

```

function getUniqueTitles(items) {

const titlesOnly = items.map(item =\> item.schoolName);

**return** [… **new** Set(titlesOnly)];

```
} 

```

function buildOptions(uniqueList) {

**return** uniqueList.map(curr =\> {

**return** {label:curr, value:curr};

```
    }); 

} 

```

}

export function schooldrop\_change(event,$w){

const matchRow = $w(“#Chin\_Schools”).rows.find( e =\> e.firstName === $w(“#schooldrop”).value );  
const requiredValue = matchRow.cat;

}

export function SearchButton\_click(event) {  
let searchGender = $w(“#GenderSelect”).value;  
let searchTown = $w(“#Town”).value;  
let searchPostcode = $w(“#Postcode”).value;  
let searchcat = 1;  
$w(“#dataset1”).setFilter(wixData.filter()  
.contains(“gend1618”, searchGender)  
.contains(“town”, searchTown)  
.contains(“pcode”, searchPostcode)  
.eq(“top3SubjectRankGroup”, searchcat)  
);

}

---

<div class="post-metadata">

**Author:** ![J.D](https://sea1.discourse-cdn.com/wix/user_avatar/forum.wixstudio.com/j.d/32/7312_2.png) [@J.D](https://forum.wixstudio.com/u/J.D)\
**Post date:** [May 28, 2019, 7:42am UTC](https://forum.wixstudio.com/t/vlookup-equivalent-code/19302/6 "2019-05-28T07:42:46Z")

</div>

@stephenxj what I wrote before was only an example.  
There’s no point in using e.firstName if firstName is not a column key in your table.  
My code actually saying:  
Find element (i.e table row) where the value of field key X is Y.
