•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Need to return a value based on two different search criteria, multiple lines, VLOOKUP
Our take
In your quest to streamline data analysis in Excel, you're looking to return a value based on two distinct search criteria using VLOOKUP or XLOOKUP. You need a formula that first identifies a specific value in Column A and then searches for another value in Column B below that match, ultimately returning the corresponding value from Column C in the same row as your second match.
Hi All,
I have a report that I run at work that doesn't come out in a way that makes it easy to work with in an excel spread sheet. The best i can get it is a txt file that when pasted spreads values into different cells. I use VLOOKUP in other sheets to find one value and return another value in the same row. I don't know how to (or if it's even possible) to do this with multiple values on different rows.
Could really use some help.
I need some sort of VLOOKUP/XLOOKUP type of formula that does the following:
-Searches Column A for a specific value
-Then Searches Column B (Below where Column A value was found) for another value
-Returns the value in Column C (Same Row as B) based on the value from B
I'm pretty much stuck using Excel for this as my company doesn't allow outside/other programs to further organize the data.
Macros may also be an option if anyone knows any.
Thanks in advance with any answers.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Return value based on word found in string of text in a cell?I am building a spreadsheet to help budget some expenses and am trying to automate as much as I can. I have a table in Column H with a keyword like "Gas" or "Electric" and then a corresponding category in Column I that groups this into something like "utilities". I have a spreadsheet where I export my bank statement and Column B is the vendor and Column C is the amt. I have Column D trying to group all the expenses based on the helper table in Columns H and I to quick and easy organize all the transactions for a month. I made the formula: =IFERROR(XLOOKUP(B3,ISNUMBER(SEARCH($H$3:$H$35,B2)),I:I),"Uncategorized") to try and get a return and it doesn't work just yet. I feel like I'm close but I'm not as familiar with XLOOKUP as I want and I cant figure out what I could use to make return any easier. Is there a simpler way to get a return like this from a string of text or am I missing something with how the XLOOKUP works? submitted by /u/Pmme10doller_psncard [link] [comments]
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Trying to build an Excel ‘database search’ that opens another file to the right row — possible?”So I have this mini project that I wanted to do. I genuinely wanted to help my co-workers with their databases (or data banking as they say). The goal is to create an excel file wherein when they could search for a person using either an ID or name and they could click something like a link or a button and then it'll open another excel file/s and get to the exact row based on the ID and/or name. The files that'll be opened could vary so I was thinking of buttons that opens the file and highlight or locates the exact row based on the ID. I'm not sure if this could be done. I wanted to give this a try but I just don't know the term to search for. Is this doable? Any ideas how or if there are other suggestions? I'm not an expert at Excel but maybe above average. I know a little bit of VBA but I'm willing to learn. Hope someone could help to search for the right term. submitted by /u/richako [link] [comments]
- Search values in 1 array then return the value of another column, same row.Using the example table, I'd like to search column 1 for data limited to a range of 0.220 to 0.200 (r5c1 here) and return the value in another colum of the same row (r5c4 here). Thanks https://preview.redd.it/b1127zmvf8qg1.png?width=302&format=png&auto=webp&s=d5a89be7b832ac7536f171eedd7fd3e78c028dce submitted by /u/SkyChef [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#Excel compatibility#financial modeling with spreadsheets#Excel alternatives#rows.com#row zero#cloud-based spreadsheet applications#AI formula generation techniques#big data management in spreadsheets#conversational data analysis#google sheets#real-time data collaboration#intelligent data visualization#data visualization tools#formula generator#enterprise data management#big data performance#data analysis tools