# Questions tagged [lookup]

The tag has no usage guidance.

138 questions
Filter by
Sorted by
Tagged with
9 views

### Google Sheets: Return a list of names if a column in the same row is empty [migrated]

I have the following values in Sheet A: A B 1 Josh Awarded XP 2 Jane (blank) 3 Peter (blank) 4 Mary (blank) 5 Josh (blank) 6 Peter (blank) I want to reflect the above ...
27 views

### Too much arguments shown when used LOOKUP

I'm using formula LOOKUP in EXCEL 2013 as shown below: =LOOKUP(1,0/((\$B3=Master!\$A:\$A)*(F\$2=Master!\$B:\$B)),Master!\$C:\$C,"") But now it's jump out said I entered too many arguments for this ...
47 views

### Lookup in excel from a range and returning the match

I am trying to work on this for quite a few days for now. I want to take out the middle names from last name. I have possible list of middle names and a list of last names. I am trying to look up the ...
118 views

### How to convert a simple VLOOKUP to XLOOKUP in Excel?

The VLOOKUP works as intended. The XLOOKUP is giving "#NAME" error: screenshot of the formula: the working VLOOKUP: =VLOOKUP(C12,B24:C29,2,FALSE) Which I'm reading as "take the ...
• 5
103 views

### Looking to return a number based on the date that is entered

I am looking to try and work out how to get a return from a table, depending on what it is that you enter. As an example I have included an image of how the table might look. I have a range of 5 ...
27 views

### how to use SUMIF to calculate a cell from the figures in 2 other cells based on the date

I have one cell that calculates a figure based on 2 cells, so for example target quantity is in C6 then the completed quantity is in D6 so cell E6 is "=D6-C6" but i only want it to ...
867 views

### Excel search for a string and then return a value from a list based on the match

I have a list of values in one table (Artist), that I then want to find a partial match to be used to populate a second value (Genre) from a separate table. If it cannot find a match, then leave the ...
• 19
1 vote
89 views

### Given a value, I want to find this value in one range and then report out a value in the same relative position in a different range

I have two ranges on the same sheet, we'll call them Range A and Range B. These ranges are the same size and their location on a sheet is known. I ultimately need to find a value in Range B. To start ...
103 views

### Access cached DNS entries when main DNS-server is down?

I try to access subdomain.a.com which uses dns.b.com for DNS but that DNS-server (all 10 of them actually) is down since about 2 hours. subdomain.a.com is working as expected but you just can't access ...
• 834
77 views

### What formula should I use to pull

What Excel formula can I write so it will look for the same project in Column B in Column I and place the YTD matching the project from Column J in Column C in the table below? A B ...
• 1
223 views

### How to lookup values from dropdown lists in Excel?

I have a complicated Excel worksheet, but I will simplify what I need in a simple scenario. I have the following sheet as raw data. Also, I have another sheet where I can select the student's name ...
493 views

### How two-way lookup works in excel?

The two-way table is a table where the value of a dependent variable is dependent on two independent variables, so actually it's a mix of two one-way tables. When you lookup for a value in a two-way ...
1 vote
97 views

### xLookup with two variables

Thanks in advance for your help here. Scenario: I have customers that purchase "credits" in agreed multiples of x, and I need to report when these packs have been depleted. I'm mining the ...
65 views

### Find Multiple hits of the search value across all columns & rows of a table and return a list of the results from the 1st column of the table

I have a spreadsheet that holds a list of offices in the 1st column. Each column after this holds the list of Postcodes that this office covers (only the 1st part, ie se3). Some of the postcodes are ...
1 vote
126 views

### Excel: Icon conditional formatting based on column name lookup

I am trying to create a conditional formatting rule for a table which looks up a pass mark in another table and then compares the value to that pass mark. It is meant to be as general as possible ...
110 views

### Excel find non matches reverse XMATCH

I need a formula that finds non-matching entries in array1 with the criteria array2 To make this simple (and as the actual data is confidential) array1 is {"a";"b";"c";&...
124 views

### Why does this LOOKUP function work like this?

Can you please help me understand why this LOOKUP function works as it does? The formula in yellow-highlighted cell A6 seems to return the label (from row 2) above the rightmost non-zero data value (...
512 views

### Lookup over matrix with non-numerical data

I have several minitables (title, content) organized in a matrix, like the following example: I want to do a lookup such that I put the "title" value in a cell, and return the content. So, ...
• 169
1k views

### LOOKUP Column Value based on right-most value

I have a list of footage for a film that is continually changing. They are organized in rows by REELS, and the rightmost value will be the latest version. The first row are dates and the rest are ...
2k views

### #NAME? Excel error only occurring on one computer

We are using Excel 365. I was teaching my colleague XLOOKUP yesterday but she kept getting #NAME?. What she entered was correct. She redid it in a new column in case any odd formatting was causing ...
• 23
90 views

### HOW TO SUBTRACT LAST ENTERANCE WITH PREVIOUS ONE

I need help, I have been trying for hours to figure this out. I need to keep track of material consumption and report it weekly so I need to subtract previous value of some material from current one. ...
106 views

### How to use COUNTIF with two conditions

I am attempting to count how many rows resulted in the following: Have a "Successful" launch status Have a list size that surpasses the circled area in R10 I know how to simply find which ...
3k views

### How to add my own mapping table to the dropdown menu of the "field lookup" function in the "Transform" tab of a Grafana dashboard panel?

The following functions are from the "Transform tab" in Grafana, have a look at Transformation types and options for examples. I have tested the "Rename by regex" function which ...
• 2,451
1 vote
164 views

### Need to lookup values in row and give the corresponding month in the column

I need a formula that looks up the number of sales in a month and stage and gives you the highlighted cells in the columns. It has to count the first cell that the sales start in and then if you have ...
• 11
1 vote
6k views

### VLOOKUP and HLOOKUP combo

I am struggling with the following (see snapshot) and I can't understand what I'm missing. If the id matches, I would like to extract the value of that id in when the values in col2 matches the ...
• 111
2k views

### excel formula to look at only 10 digits of the cells

I want to look at only the first 10 digits of the cells. If the cells that are in column C equals to the cells in column G (but in another tab), then I want the result from column H(from same tab of G)...
1 vote
194 views

### How to find the value of Column A on the first instance of Column B > Column C

Given the below table I have Column A as a unique key value, I am looking to return the first key value where B < C In this case the first instance is A = 3 Where B = 4 and C = 5 Can someone help ...
• 121
71 views

### How can I merge multiple individual worksheets in an Excel workbook that are in the same format (but different lengths)?

I have an inherited (and sometimes still-updated) workbook with many worksheets (currently 50+). Each of these tabs has a table in the format of: segment_name | subsegment | subnet | ipaddress ^^...
• 10.2k
19 views

Im trying to chain multiple dns server. I want that my local dns server is asked first, if no entry was found a second and third should be asked. Is this possible? My /etc/resolve.conf looks like this:...
• 423
2k views

### How to return first non-blank value with LOOKUP function

I have a table: table in which I reference data from a dataset using a LOOKUP function. I can reference columns(such as new_deaths) which have the latest(non-blank) data just fine. However, for some ...
• 5
175 views

### Excel lookup returning multiple values in a column

Everyone - I have seen numerous posts on this site similar to this, however with the way the data is laid out, none will work for what we are trying to accomplish. I have a spreadsheet with columns ...
51 views

### Look for Date values in embedded in strings

I have a value in a cell that might have a 2-digit month and 2-digit year value embedded in it, and I need a function that would return TRUE if that is found. Sample input data: sCa!1220LB6qHZbCnsEE ...
• 319
107 views

### How to get the last value of a column that is not blank and match it with a cell in a Google Sheet?

For example, the value of the cell is David. Column A has the names of students. Column B has the dates. I need to get the last date if I enter the name of a student. I was trying to use the LOOKUP ...
700 views

### Memory Exception on Excel Fuzzy Lookup Add In

I have two tables of data with 5 columns - Name, Phone Number, Email Address, Post Code and Order Time. Both tables have around 10,000 rows. I need there to be an exact match on Name, Phone Number, ...
1 vote
120 views

### Excel lookup by minimum value

I have this table: . Is it possible to look up the letter grade by the minimum percent value? For example, anything greater than or equal to 87% but less than 90% returns B+.
• 113
1 vote
94 views

### Excel - How do I look up multiple cells from a single reference?

I'm looking to somehow look up a certain amount of cells from a single reference and return them in the same order in another workbook. For example, say I have 5 different SKU codes (these aren't ...
• 11
2k views

### Finding a Row Number based upon a value within a known column?

I have a workbook that's set out in a fairly specific way when imported. And I'm one lookup away from having them quickly and easily sort itself out into a more readable form. However, without using ...
624 views

### Take the header of most-left value in excel

I'm using the formula from this post to take the most left value from my excel sheet. I’m using this formula for column “N” as shown at this excel print screen. Now I have to find the most left value,...
1 vote
141 views

### Dynamic Array lookup

The values in E should match the values in D but don't. Am I using lookup wrong? This is just a test from a larger problem I'm having. I decided to make a testbed to figure out how to use LOOKUP. My ...
138 views

### Match 5 closest in same array

I have a list of customers and lat/long coordinates (3 columns:c,u,v respectively) I want to create 5 columns to resolve as the closest 5 customers. So far I’m using min(sqrt(([lat1]-[lat_array])^2+...
22 views

### Too many arguments in use if and lookup functions

I am using an IF formula with LOOKUP in order to evaluate and return a value in a table. For example, to return a value in a salary matrix, =IF(K5>10,G5+SMI!\$G\$25,IF(I5=1,LOOKUP(K5,SMI!\$F\$10:SMI!\$...
• 11
331 views

### Lookup Range based on multiple column and row critera

I have the lookup table below, where columns C through E are different places: Below, I am trying to lookup the value based on the columnar and row criteria: How can I find the value Orange, 2019, ...
• 21
1 vote
58 views

### How to search for and return all records between two dates?

I'm not sure if this is a lookup formula I need. Basically, I have a huge spreadsheet with the travel details of lots and lots of groups. Each row is a different group. For reference, Column K shows ...
• 11
1k views

### Using AVERAGEIF() over a range of years

I have a dataset that includes year, country, and population data. A small condensed sample is below. I am trying to write an excel statement that would calculate the average population from 1980 - ...
1 vote
98 views

### Excel: Use Cell in Formula Based On Criteria

I have the following Excel workbook sheets. SHEET 1 contains data on criminal sentences handed down for specific individuals: | Name | DateOfBirth | DateOfSentence | SentenceYears | |------|---------...
• 113
1k views

### Calculate weighted scores via column heading lookup in Excel

I have a data entry sheet with multiple columns, and the user is allowed to input a number in each cell. I have a separate table with a weighting based on each Column's heading. The desired output I ...
46 views

### LOOKUP reference with mulitple outcome values

I am looking to pull out and display the multiple ages of people associated with a single reference number. Below snipped displays the sheet set up. The sheet has approximate 2500 rows: App ref,...
• 1
44 views

### Assitance with Lookup function for matching two sheets of data

Basically I have two sheets (s1 and s2), and the data is formatted something like below, first is S1: ID Name 1 Prod 1 2 Prod 2 3 Prod 3 4 Prod 4 5 Prod 5 6 Prod 6 ...
• 3
728 views

### Exclude IPs from IP list that are from Russia

Which command in Bash should I use to exclude all IPs that are from Russia from an IP list? I had in mind maybe with the help of geoiplookup. What I would really like to have as result; original-IP-...
• 21
1 vote