Hello,
try to use as the first vlookup argument cells(a,b)&" "&cells(x,y) and it should work
Regards,
Alex
Possible to do a v-lookup using "and" to look up both first and last names (currently in two columns)? Alternatively, suppose I could combine first and last... Other ideas?
Hello,
try to use as the first vlookup argument cells(a,b)&" "&cells(x,y) and it should work
Regards,
Alex
Maybe:
=VLOOKUP(E1&F1,$A$1:$B$21,2,0)
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Assuming too much and qualifying too much are two faces of the same problem.
Without the need for Ctrl+Shift+Enter:
=INDEX(C$1:C$5,MATCH(1,INDEX((A$1:A$5=D1)*(B$1:B$5=E1),),FALSE))
Microsoft MVP - Excel
Andrew,
=INDEX(C$1:C$5,MATCH(1,INDEX((A$1:A$5=D1)*(B$1:B$5=E1),),FALSE))
the second index isn't going to return anything right? The above mentioned formula will return C$1:C$5 if D1 =A$1:A$5 and E1=B$1:B$5 right?
Φ(𝑘)𝛼Α𝑡
O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste
The second INDEX is just to avoid having to confirm with Ctrl+Shift+Enter.
Microsoft MVP - Excel
noted.
in:
INDEX(ResultRange,MATCH(1,IF(CdtRange1=cdt1,IF(cdtRange2=cdt2,1)),0))
The ResultRange here is contained in a column.
Is it possible to extend said INDEX to several columns eg A2:I6 and add to the present MATCH one more condition CdtRange3?
CdtRange1 being in a Column (B2:B6)
CdtRange2 being in a Column (A2:A6)
CdtRange3 being in a Row (C1:I1)
Thanks to share your opinion.
Φ(𝑘)𝛼Α𝑡
O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste
You have 7 columns there, but only 5 rows. Maybe it would help if you posted some sample data with the expected result.
Microsoft MVP - Excel
considering a raw data not limited to this range:
(We do have more occurrence of Genus per month and level of conchio per reading.)HTML Code:genus Month conchio1 conchio2 conchio3 conchio4 conchio5 conchio6 conchio7 maxima Jan-12 12 13 14 23 34 34 23 margtfr Feb-12 11 11 11 12 14 14 12 fragls Mar-12 44 56 68 76 8 8 76 gigas Apr-12 -6 5 2 2 5 6 8 crocea Feb-12 7 9 5 8 9 2 3
the output would look like:
This goes down to conchio7 returning the corresponding values from table 1 if conditions are met.HTML Code:level genus Jan-12 Feb-12 Mar-12 Apr-12 conchio1 maxima 12 conchio1 margtfr 11 conchio1 fragls 44 conchio1 gigas -6 conchio1 crocea 7 conchio2 maxima 13 conchio2 margtfr 11 conchio2 fragls 56 conchio2 gigas 5 conchio2 crocea 9
Presently using this:
=IFERROR(IF($A17="a",INDEX($C$2:$C$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
IF($A17="b",INDEX($D$2:$D$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
IF($A17="c",INDEX($E$2:$E$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
IF($A17="d",INDEX($F$2:$F$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
IF($A17="e",INDEX($G$2:$G$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
IF($A17="f",INDEX($H$2:$H$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
IF($A17="g",INDEX($I$2:$I$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0))))))))),"")
This works but it is also absolutely cumbersome.
Φ(𝑘)𝛼Α𝑡
O365P+ Post sample data using one of these: MrExcel HTML Maker Excel jeanie Forum Tools add-in or Borders-Copy-Paste
Like this thread? Share it with others