-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Return value based on 2 other columns
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
What are the conditions columns B and BJ must meet?
Upvote
0
Sorry,yup,not explained very well!
Sheet 1 has all my data. (rows 2 to 34500)
Column B and Column BJ are headed up simply as "B" and "BJ"
Sheet2 I have a table where I am to populate the information
In Column A (sheet2) I have the LOOKUP for Column B of Sheet 1
In Row 2,(Sheet2) I have the LOOKUP for Column B of Sheet 2
I hope that explains it a bit better!
Sheet 1 has all my data. (rows 2 to 34500)
Column B and Column BJ are headed up simply as "B" and "BJ"
Sheet2 I have a table where I am to populate the information
In Column A (sheet2) I have the LOOKUP for Column B of Sheet 1
In Row 2,(Sheet2) I have the LOOKUP for Column B of Sheet 2
I hope that explains it a bit better!
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
I'm sorry you don't.
Given:
jon,x
dan,y
in A:B, we might want to have "dan" because column B = y. This y is called a condition. Thus, give me all of the names from A where B = y is a clear and intelligible problem statement.More you don't have to feed a would-be helper, unless you get additional questions.
Given:
jon,x
dan,y
in A:B, we might want to have "dan" because column B = y. This y is called a condition. Thus, give me all of the names from A where B = y is a clear and intelligible problem statement.More you don't have to feed a would-be helper, unless you get additional questions.
Upvote
0
I am not sure I follow, but on sheet1 where is have my data, in column B which is the first match criteria, I have to match values AA, BB, CC, DD, EE, FF, GG, HH, JJ, KK, LL, MM, NN, and PP
On Sheet1 the other column I need to match up is in column BJ. In this column there are around 100 different values.
So if on sheet 1 I have (for example)
---B----| ---BJ---|---AF---|
-------------------------------
---AA---|---xx---|1601
---AA---|---yy---|5873
---AA---|---zz---|3698
---AA---|---ww--|3691
---AA---|---vv---|2699
So Column D is headed "WW", and Row 5 is "BB".
Column AF has the value 6481, which is what I want it to return
On Sheet1 the other column I need to match up is in column BJ. In this column there are around 100 different values.
So if on sheet 1 I have (for example)
---B----| ---BJ---|---AF---|
-------------------------------
---AA---|---xx---|1601
---AA---|---yy---|5873
---AA---|---zz---|3698
---AA---|---ww--|3691
---AA---|---vv---|2699
---BB---|---xx---|7632
---BB---|---yy---|3569
---BB---|---zz---|8721
---BB---|---ww--|6481
---BB---|---vv---|6994
---CC---|---xx---|6435
---CC---|---yy---|1399
---CC---|---zz---|0692
---CC---|---ww--|1366
---CC---|---vv---|5555
On sheet2 is the table I want to get the data looked up
So in column A on sheet2 I need to look up the data in the table above from column B (sheet1)
And in Columns D:O on sheet2, need to look up the data in the table above from column BJ (sheet1)
---BB---|---yy---|3569
---BB---|---zz---|8721
---BB---|---ww--|6481
---BB---|---vv---|6994
---CC---|---xx---|6435
---CC---|---yy---|1399
---CC---|---zz---|0692
---CC---|---ww--|1366
---CC---|---vv---|5555
On sheet2 is the table I want to get the data looked up
So in column A on sheet2 I need to look up the data in the table above from column B (sheet1)
And in Columns D:O on sheet2, need to look up the data in the table above from column BJ (sheet1)
So Column D is headed "WW", and Row 5 is "BB".
Column AF has the value 6481, which is what I want it to return
Last edited:
Upvote
0
Hi
Following on from this, I have found a solution, however, I now need it to fin the MINIMUM value in cell BJ if there are multiple instances
How can I amend the above to find the minimum value that meets the criteria please?
Following on from this, I have found a solution, however, I now need it to fin the MINIMUM value in cell BJ if there are multiple instances
Code:
=INDEX(Sheet1!$AF$2:$AF$33461,MATCH($A4,IF(Sheet1!$BJ$2:$BJ$33461=D$2,Sheet1!$B$2:$B$33461),0))How can I amend the above to find the minimum value that meets the criteria please?
Upvote
0
